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.

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top