Can a foreign key relate to a primary key on either of two tables?
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.
This was first published in September 2005