从2个表中删除MySql级联

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.

我有一个使用类表继承的MySql架构,但我希望子表从父表和外表中删除级联。

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.

问题是当从其中一个外表删除记录时,它将从子表中删除记录,但不从父表中删除。我不想使用存储过程/多语句作为解决方案,因为外部表有自己的级联删除,所以我也需要存储过程。

1 个解决方案

#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.

当删除它所引用的表中的行(父)时,ON DELETE CASCADE将使用外键(子)删除表中的行。如果没有ON DELETE CASCADE,带有外键(子)的行将指向不再存在的行(父),您将获得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.

反过来没有这样的问题,删除子节点而不删除父节点不会留下孤立的行,就MySQL而言也没有INTEGRITY CONSTRAINT VIOLATION,并且不需要级联。

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)

首先删除子项,然后删除父项,然后删除另一项记录(不需要ON DELETE CASCADE)

Delete the child first, then the other record and then the parent (no need for the ON DELETE CASCADEs)

首先删除子项,然后删除其他记录,然后删除父项(不需要ON DELETE CASCADE)

Delete the parent first, then the other record (only need ON DELETE CASCADE on the parent reference)

首先删除父项,然后删除另一项记录(仅在父项引用上需要ON DELETE CASCADE)

Delete the other record first then the parent (only need ON DELETE CASCADE on the other reference)

先删除其他记录然后再删除父记录(另一个参考只需要ON DELETE CASCADE)

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)

在子表上的AFTER DELETE上放置一个触发器,删除父记录和其他记录(按任意顺序),然后删除子记录将清除所有三个记录(不需要ON DELETE CASCADE)

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.

如果您可以将关系更改为另一个表(a或b),以便它引用子项而不是引用另一个表的子项(如您当前所示),并保持ON DELETE CASCADE,则删除父行将清除孩子,然后轮流的另一个记录。

#1