On Delete Set Null

from the Artful MySQL Tips List

How to set up a foreign key for ON DELETE SET NULL, ie when a parent row is deleted, matching key values in child rows are set to NULL:
drop table if exists b,a;
create table a(id int primary key)engine=innodb;
insert into a values(1),(2);
create table b(
  id int primary key,
  aid int,
  foreign key(aid) references a(id) on delete set null
insert into b values(1,1),(2,1);
delete from a where id=1;
select * from b;
| id | aid  |
|  1 | NULL |
|  2 | NULL |

Last updated 22 May 2009

Return to the Artful MySQL Tips page