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 |
+-----+------+------+

Last updated 21 Apr 2024