Q

Two foreign keys to the same primary key?

I have two tables, tableA with ID as a primary key and tableB which has two fields (apart from several other fields)...

that are IDs from tableA. Can I create a foreign key relationship between them? The basic relationship is one-to-many.

Yes. In fact, this is quite common:

create table tableA
     ( ID     integer    not null 
     , AName  varchar(5) not null
     , constraint A_pk 
         primary key (ID)
     )
create table tableB
     ( AID1   integer  not null
     , AID2   integer  not null
     , Bstuff varchar(9) not null
     , constraint B_pk 
         primary key (AID1,AID2)
     , constraint B_fkA1 
         foreign key (AID1)
              references tableA (ID)
     , constraint B_fkA2 
         foreign key (AID2)
              references tableA (ID)
     )

This was last published in February 2004

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close