mysql - Many-to-many relationship with compound primary key constraint -

i got requirement many-to-many relationship design in dbms other constraints.

there 2 tables, t1 , t2 t1.id , t2.id primary keys respectively. t1 , t2 has many-to-many relationship, natural design add third table t3 foreign keys. e.g.,

t3: id t1_id     (foreign key refereed t1.id) t2_id     (foreign key refereed t2.id) 

another requirement pair(t1.id, t2.id) should have one-to-one relationship t4.id. add t5 primary_key(t1.id, t2.id)? or can directly using t3's t1_id, t2_id compound primary key? need fast scan whether pair(t1.id, t2.id) has entity in t4, i.e., pair(t1.id, t2.id) has one-to-one relationship t4.id.

appreciate if give me hints.

if have enforce one-to-one relationship between (t1.id,t2.id) , t4.id, seems indicate (t1.id,t2.id) unique.

if that's case, if (t1.id,t2.id) should unique in t3, make primary key t3.

you add foreign key reference t4, , make unique no more 1 row in t3 can related t4. or, can make foreign key reference go other way, , store primary key values of t3 in t4.

for example, make foreign key in t3 t4

 create table t3  ( t1_id  int not null       comment 'pk, fk ref t1'  , t2_id  int not null       comment 'pk, fk ref t2'  , t4_id  int                comment 'fk ref t4'  , primary key (t1_id,t2_id)  , constraint t3_ux2 unique key (t4_id)  , constraint fk_t3_t1 foreign key (t1_id) references t1(id)      on delete cascade on update cascade  , constraint fk_t3_t2 foreign key (t2_id) references t2(id)      on delete cascade on update cascade  , constraint fk_t3_t4 foreign key (t4_id) references t4(id)      on delete restrict on update cascade  ) ... 

or, introduce surrogate primary key on t3. (we typically if there other tables have foreign key reference t3; because t3 acting more actual entity, pure relationship. , avoids having use composite key foreign key in table reference t3.

for example:

 create table t3  ( id     int not null       comment 'pk'  , t1_id  int not null       comment 'fk ref t1'  , t2_id  int not null       comment 'fk ref t2'  , primary key (t3_id)  , constraint t3_ux1 unique key (t1_id,t2_id)  , constraint fk_t3_t1 foreign key (t1_id) references t1(id)       on delete cascade on update cascade  , constraint fk_t3_t2 foreign key (t2_id) references t2(id)       on delete cascade on update cascade  ) ... 

you add foreign key in table t3 reference t4, in previous example.

or implement relationship t4 reference t3.

either way works.

my decision based on

1) avoiding composite keys foreign keys (if no entity tables have composite keys)

2) want or need on delete cascade functionality, , way need work... delete t3 should cascade deletes t4, or other way around.

i showed on delete restrict in example gave adding t4_id fk column t3. figured delete t4 should not "break" relationship between t1 , t2 removing rows t3.