I have a check table that contains both medical and dental checks. I also have a dental table and a medical table. My question is if there is a way to have a foreign key on the check table, on claim no, that relates back to a primary key, on claim no, that could be on either the medical or dental table.
You cannot have a Foreign Key refer to two different tables. However, those two tables can have Foreign Keys that refer to the same parent table. So both MEDICAL and DENTAL could refer to CHECKS, but CHECKS cannot have the same column refer to both MEDICAL and DENTAL.
If you need a more complex FK that the database cannot provide, like the last example I gave, then you can use a trigger to enforce your referential integrity. You can code a trigger to look in the MEDICAL or CHECKS field for referential integrity depending on the value in a column in the CHECKS table. You will have to code this yourself.
Dig deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.