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.

Answer 1

ON DELETE CASCADE will delete the row in the table with the foreign key (child) when the row in the table it is referencing (parent) is deleted. Without ON DELETE CASCADE, the row with the foreign key (child) would be left pointing at a row (parent) which no longer exists and you would get an INTEGRITY CONSTRAINT VIOLATION.

There is no such problem the other way round, deleting the child without deleting the parent leaves no orphaned rows and no INTEGRITY CONSTRAINT VIOLATION as far as MySQL is concerned and no cascade is necessary.

If you wish to delete the child, parent and the other referenced row together you have a few options.

Multistatement/Procedure:

• Delete the child first, then the parent and then the other record (no need for the ON DELETE CASCADEs)
• Delete the child first, then the other record and then the parent (no need for the ON DELETE CASCADEs)
• Delete the parent first, then the other record (only need ON DELETE CASCADE on the parent reference)
• Delete the other record first then the parent (only need ON DELETE CASCADE on the other reference)

Trigger:

• Place a trigger on AFTER DELETE on the child table that deletes the parent and the other record (in either order), then deleting the child will clear all three records (no need for the ON DELETE CASCADEs)

Change the relationship:

• If you can change the relationship to the other table (a or b) so that it references the child and not the child referencing the other table (as you have currently), and keep the ON DELETE CASCADEs, deleting the parent row will clear the child and then the other record in turn.