MySql cascade delete from 2 tables

Question

I have a MySql schema which uses class table inheritance, but I want the child tables to have cascade delete from the parent table, and a foreign table.

create table parent (
  _key bigint unsigned not null,
  name varchar(64) unique not null,
  primary key(_key)
);

create table child_a (
  _key bigint unsigned not null,
  foreign_key_a bigint unsigned not null,
  foreign key(_key) references parent(_key) on delete cascade,
  foreign key(foreign_key_a) references a(_key) on delete cascade,
  primary key(_key)
);

create table child_b (
  _key bigint unsigned not null,
  foreign_key_b bigint unsigned not null,
  foreign key(_key) references parent(_key) on delete cascade,
  foreign key(foreign_key_b) references b(_key) on delete cascade,
  primary key(_key)
);

The issue is when a record is deleted from one of the foreign tables, it will delete the record from the child table, but not from the parent table. I would not like to use a stored procedure / multi-statement as a solution because the foreign tables have cascade deletes of their own, so I would need stored procedures for those as well.

MySql cascade delete from 2 tables

1 Answers