Q

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.

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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

1 comment

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close