Cascade

from the Artful MySQL Tips List


On Delete Cascade and On Delete Update tell the SQL engine to cascade changes in parent keys to matching rows in the child table where the Foreign Key is defined ...

drop table if exists c,p;
create table p( pid int primary key );
create table c (
  cid int primary key, 
  pid int, 
  foreign key(pid) references p(pid) on delete cascade on update cascade
);
insert into p values(1),(2),(3),(4);
insert into c values(1,1),(2,2);
select p.pid,c.cid,c.pid from p left join c using(pid);
+-----+------+------+
| pid | cid  | pid  |
+-----+------+------+
|   1 |    1 |    1 |
|   2 |    2 |    2 |
|   3 | NULL | NULL |
|   4 | NULL | NULL |
+-----+------+------+
delete p,c from p join c using(pid) where p.pid=2;
update p set pid=5 where pid=1;
select p.pid,c.cid,c.pid from p left join c using(pid);
+-----+------+------+
| pid | cid  | pid  |
+-----+------+------+
|   3 | NULL | NULL |
|   4 | NULL | NULL |
|   5 |    1 |    5 |
+-----+------+------+


Return to the Artful MySQL Tips page