0

Suppose I have tableA with A.id, tableB with B.id and table C with C.id. Now I want to create a table D that has a primary composite key(A.id, B.id, C.id). How can I do this?

create tableD(

    A.id ..
    B.id ..,
    C.id ..,
    
    primary key (A.id, B.id, C.id),
    foreign key (A.id, B.id, C.id) references ??
)
2
  • 3
    You can't; you will create three foreign keys referencing each parent table individually.
    – mustaccio
    Jan 6 at 20:15
  • You can't create a primary key in one table on fields that only exist in another table. If you create your table so it actually has the 3 fields (Aid, Bid, Cid) (for example) stored in TableD then you can create a composite primary key on it. Or you can create three foreign keys, one to each field referencing each correlating table, as mustaccio mentions too. In either case, you do need to duplicate the fields in your new table TableD.
    – J.D.
    Jan 7 at 3:21

1 Answer 1

1

So you have one table with a composite Primary Key, each element of which references the Primary Key of another table.
Without specifics, let's take a simplistic example of a Student taking a Class taught by a specific Teacher. Each Entity (Student, Class, Teacher) can exist on its own (i.e. has its own table) and the association of the three is a student actually taking a class (OK, I'm ignoring dates in all this!).

create tableA ( id ... primary key ( id ) ) ;
create tableB ( id ... primary key ( id ) ) ;
create tableC ( id ... primary key ( id ) ) ;

create tableD
( a_id ...
, b_id ...
, c_id ...
, foreign key ( a_id ) references tableA ( id ) 
, foreign key ( b_id ) references tableB ( id ) 
, foreign key ( c_id ) references tableC ( id ) 
); 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.