1. 首页
  2. 问题
  3. MySQL - 删除具有引用自身的外键约束的行
一尘不染

MySQL - 删除具有引用自身的外键约束的行

mysql

我有一张表，其中存储了用户在我的网站上发布的所有论坛消息。消息层次结构是使用嵌套集模型实现的。

以下是表的简化结构：

  • Id (PRIMARY KEY)
  • Owner_Id (FOREIGN KEY REFERENCES TO Id)
  • Parent_Id (FOREIGN KEY REFERENCES TO Id)
  • nleft
  • nright
  • nlevel

现在，表格看起来像这样：

+ ------- + ------------- + -------------- + ---------- + ----------- + ----------- +
| Id      | Owner_Id      | Parent_Id      | nleft      | nright      | nlevel      |
+ ------- + ------------- + -------------- + ---------- + ----------- + ----------- +
| 1       | 1             | NULL           | 1          | 8           | 1           |
| 2       | 1             | 1              | 2          | 5           | 2           |
| 3       | 1             | 2              | 3          | 4           | 3           |
| 4       | 1             | 1              | 6          | 7           | 2           |
+ ------- + ------------- + -------------- + ---------- + ----------- + ----------- +

注意第一行是根消息，这篇文章的树可以显示为：

-- SELECT * FROM forumTbl WHERE Owner_Id = 1 ORDER BY nleft;

MESSAGE (Id = 1)
    MESSAGE (Id = 2)
        Message (Id = 3)
    Message (Id = 4)

当我尝试Owner_Id在单个查询中删除同一行下的所有行时，就会出现我的问题。例子：

DELETE FROM forumTbl WHERE Owner_Id = 1 ORDER BY nright;

上述查询失败并出现以下错误：

错误代码：1451。无法删除或更新父行：外键约束失败（forumTbl, CONSTRAINT Owner_Id_frgnFOREIGN KEY ( Owner_Id) REFERENCES forumTbl( Id) ON DELETE NO ACTION ON UPDATE NO ACTION）

原因是第一行，即根节点（Id=1），在其Owner_Id字段（Owner_Id=1）中也具有相同的值，并且由于外键约束导致查询失败。

我的问题是：如何防止这种外键约束循环并删除引用自身的行？有没有办法做到这一点，而无需先将Owner_Id根行的 更新为NULL？

我创建了这个场景的演示：http ://sqlfiddle.com/#!9/fd1b1

谢谢你。


阅读 70

收藏
2022-10-13

共1个答案

一尘不染

  1. 除了禁用危险且可能导致不一致的外键外，还有两个其他选项需要考虑：

  2. 使用选项修改FOREIGN KEY约束ON DELETE CASCADE。我还没有测试所有情况，但你肯定需要这个作为(owner_id)外键，也可能需要另一个。

sql ALTER TABLE forum DROP FOREIGN KEY owner_id_frgn, DROP FOREIGN KEY parent_id_frgn ; ALTER TABLE forum ADD CONSTRAINT owner_id_frgn FOREIGN KEY (owner_id) REFERENCES forum (id) ON DELETE CASCADE, ADD CONSTRAINT parent_id_frgn FOREIGN KEY (parent_id) REFERENCES forum (id) ON DELETE CASCADE ;

如果你这样做，那么从树中删除一个节点和所有后代会更简单。您删除一个节点，并通过级联操作删除所有后代：

sql DELETE FROM forum WHERE id = 1 ; -- deletes id=1 and all descendants

  1. 您进入的问题实际上是 2 个问题。首先是从具有自引用外键的表中删除对于 MySQL 来说不是一个严重的问题，只要没有引用自身的行。如果有一行，如您的示例所示，则选项是有限的。禁用外键或使用CASCADE操作。但是如果没有这样的行，删除就成了一个小问题。

因此，如果我们决定存储NULL而不是相同id的 in owner_id，那么您可以在不禁用外键和级联的情况下删除。

然后你会偶然发现第二个问题！运行您的查询会引发类似的错误：

sql DELETE FROM forum WHERE owner_id = 1 OR id = 1 ;

Error(s), warning(s):
Cannot delete or update a parent row: a foreign key constraint fails (rextester.forum, CONSTRAINT owner_id_frgn FOREIGN KEY (owner_Id) REFERENCES forum (id))

此错误的原因将与以前不同。这是因为 MySQL 在删除每一行之后检查每个约束，而不是在语句的末尾（应该如此）。因此，当父级在其子级被删除之前被删除时，我们会得到一个外键约束错误。

幸运的是，有一个简单的解决方案，嵌套集合模型和 MySQL 允许我们设置删除的顺序。我们只需要 order bynleft DESC或 by nright DESC，这样可以确保在父级之前删除所有子级：

sql DELETE FROM forum WHERE owner_id = 1 OR id = 1 ORDER BY nleft DESC ;

次要注意，我们可以（或应该）使用考虑嵌套模型的条件。这是等效的（并且可能使用索引(nleft, nright)来查找要删除的节点：

sql DELETE FROM forum WHERE nleft >= 1 AND nright <= 8 ORDER BY nleft DESC ;

2022-10-13