CODEWITHSUNDEEP
mysqlsql
Oscar
Oscar

Reputation: 2047

Foreign key relationship with composite primary keys in MySQL

Which is the best way to create a relationship between two tables when referenced table has a composite primary key?

table1{
   id,
   name
}

table2{
   id1,
   id2,
   name
}PrimaryKey(id1, id2)

Upvotes: 2

Views: 1867

Answers (2)

Sr rodriguez
Sr rodriguez

Reputation: 36

One way is this

alter table t add constraint fk_t_id1_id2
foreign key (id1, id2) references table2(id1, id2);

As Gordon said, the best option is create an auto incremental ID and make this the primary key.

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

The only way is using both keys:

alter table t add constraint fk_t_id1_id2
    foreign key (id1, id2) references table2(id1, id2);

However, I would encourage you to add an auto-incrementing column to table2 so such relationships can use a single key.

Upvotes: 4

Related Questions