Ask the Expert

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

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

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: