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,
  key(aid),
  foreign key(aid) references a(id) on delete set null
)engine=innodb;
insert into b values(1,1),(2,1);
delete from a where id=1;
select * from b;
+----+------+
| id | aid  |
+----+------+
|  1 | NULL |
|  2 | NULL |
+----+------+


Return to the Artful MySQL Tips page