Two foreign keys to the same primary key?

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.

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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 first published in February 2004

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