CASCADE在多对多自引用表中删除 [英] CASCADE Delete in many-to-many self-reference table

问题描述

下面的表格 DISPLAY_TAB 是一个自我参考表格，可以包含父标签和子标签。父母标签可以有多个子标签，子标签可以属于多个父母。

我想在主表和关系表 DISPLAY_TAB_GROUPING 之间建立一个CASCADE DELETE关系，因此当删除父或子选项卡时，也会自动删除（只是关系，而不是实际的标签记录）。因此，我在 DISPLAY_TAB_GROUPING 上为表TAB_ID_R_1和TAB_ID_R_2表创建FOREIGN KEY约束，并在 DISPLAY_TAB 表中引用TAB_ID。它工作正常，它为我的关系之一添加ON DELETE CASCADE时正常工作，但是当我尝试它的两个 - 它会抛出一个错误，这个可能会导致周期或多个级联路径 。

如何为两种关系设置ON CASCADE DELETE？我不明白这是如何引起周期的 - 每次在主表中删除记录时，只删除关系表中的相关记录。

src =https://i.stack.imgur.com/7v77J.pngalt =在这里输入图片描述>

解决方案

请阅读这篇知识库文章，其中包括以下内容...

您收到此错误信息是因为在SQL Server中，一个表不能
在所有级联引用
由DELETE或UPDATE语句启动的操作。
例如，级联参照动作树必须只有
一条路径到级联参照动作
树上的一个特定的表。

要做你想做的事情，DISPLAY_TAB_GROUPING表必须出现两次。我建议你使用一个实现你的删除代码的存储过程。

Table DISPLAY_TAB below is a self-reference table that can contain both parent and child tabs. A parent tab can have multiple child tabs and a child tab can belong to multiple parents.

I'd like to establish a CASCADE DELETE relationship between main table and relationship table DISPLAY_TAB_GROUPING so when either parent or child tab is deleted - relationship is automatically deleted as well (just relationship, not actual tab record). So I am creating a FOREIGN KEY constrain on DISPLAY_TAB_GROUPING for fields TAB_ID_R_1 and TAB_ID_R_2 tables, referencing TAB_ID in DISPLAY_TAB table. And it works fine, it works fine when I add ON DELETE CASCADE for one of the relationship, but when I try it for both - it throws an error that this "May cause cycles or multiple cascade paths".

How do I set ON CASCADE DELETE for both relationships? I don't see how this can cause cycles - every time a record in main table is deleted only related record(s) in relationship tables should be deleted.

解决方案

Read this KB article, which says the following among other things...

You receive this error message because in SQL Server, a table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement. For example, the tree of cascading referential actions must only have one path to a particular table on the cascading referential actions tree.

To do what you want, the DISPLAY_TAB_GROUPING table would have to appear twice. I suggest you use a stored proc that implements your delete code instead.

这篇关于CASCADE在多对多自引用表中删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！