Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: