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 |


Return to the Artful MySQL Tips page