Re: Foreign key index on multiple columns

[Peter Brawley]

Easy to check ...

drop table if exists c,p;
create table p(pid int unsigned primary key);
create table c(
  cid int unsigned primary key,
  pid int unsigned,
  i int not null,
  foreign key(pid) references p(pid) on delete cascade on update cascade,
  key(pid,i)
);
insert into p set pid=1;
insert into c set cid=1,pid=1,i=1;
update p set pid=2;
select p.pid,c.cid,c.pid,i 
from p 
left join c using(pid);
+-----+------+------+------+
| pid | cid  | pid  | i    |
+-----+------+------+------+
|   2 |    1 |    2 |    1 |
+-----+------+------+------+