我有一张表,其中存储了用户在我的网站上发布的所有论坛消息。消息层次结构是使用嵌套集模型实现的。
以下是表的简化结构:
现在,表格看起来像这样:
+ ------- + ------------- + -------------- + ---------- + ----------- + ----------- + | 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在单个查询中删除同一行下的所有行时,就会出现我的问题。例子:
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)
forumTbl
Owner_Id_frgn
Id
原因是第一行,即根节点(Id=1),在其Owner_Id字段(Owner_Id=1)中也具有相同的值,并且由于外键约束导致查询失败。
Id=1
Owner_Id=1
我的问题是:如何防止这种外键约束循环并删除引用自身的行?有没有办法做到这一点,而无需先将Owner_Id根行的 更新为NULL?
NULL
我创建了这个场景的演示:http ://sqlfiddle.com/#!9/fd1b1
谢谢你。
除了禁用危险且可能导致不一致的外键外,还有两个其他选项需要考虑:
使用选项修改FOREIGN KEY约束ON DELETE CASCADE。我还没有测试所有情况,但你肯定需要这个作为(owner_id)外键,也可能需要另一个。
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
CASCADE
因此,如果我们决定存储NULL而不是相同id的 in owner_id,那么您可以在不禁用外键和级联的情况下删除。
id
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,这样可以确保在父级之前删除所有子级:
nleft DESC
nright DESC
sql DELETE FROM forum WHERE owner_id = 1 OR id = 1 ORDER BY nleft DESC ;
次要注意,我们可以(或应该)使用考虑嵌套模型的条件。这是等效的(并且可能使用索引(nleft, nright)来查找要删除的节点:
(nleft, nright)
sql DELETE FROM forum WHERE nleft >= 1 AND nright <= 8 ORDER BY nleft DESC ;