Re: delete with inner join doesnt work

[Peter Brawley]

Yes that's exactly what On Delete Cascade means ...

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 * from p left join c using(pid);
delete p,c from p join c using(pid) where p.pid=2;
select * from p left join c using(pid);

InnoDB now auto-maintains a FK index.