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