MS SQL“ON DELETE CASCADE”多个外键指向同一个表？

Howdy, I have a problem where i need a cascade on multiple foreign keys pointing to the same table..

你好，我有一个问题，我需要在指向同一个表的多个外键上级联..

[Insights]
| ID | Title        |
| 1  | Monty Python |
| 2  | Spamalot     | 

[BroaderInsights_Insights]
| broaderinsight_id | insight_id |
| 1                 | 2          |

Basically when either record one or two in the insights table is deleted i need the relationship to also be deleted..

基本上，当洞察表中的一个或两个记录被删除时，我需要删除该关系。

I've tried this:

我试过这个：

 CREATE TABLE broader_insights_insights(id INT NOT NULL IDENTITY(1,1),
   broader_insight_id INT NOT NULL REFERENCES insights(id) ON DELETE CASCADE,
   insight_id INT NOT NULL REFERENCES insights(id) ON DELETE CASCADE,
   PRIMARY KEY(id))
Go

This results in the warning that the cascade "may cause cycles or multiple cascade path"

这导致警告级联“可能导致循环或多个级联路径”

So ive tried adding a cascade to just the insight_id and this results in:

所以我尝试添加一个级联到insight_id，这导致：

"The DELETE statement conflicted with the REFERENCE constraint"

“DELETE语句与REFERENCE约束冲突”

Any ideas?

有任何想法吗？

Thanks

谢谢

Daniel

丹尼尔

1 个解决方案

#1

You'll have to implement this as an INSTEAD OF delete trigger on insights, to get it to work. Something like:

您必须在洞察中将其实现为INSTEAD OF删除触发器，以使其发挥作用。就像是：

create trigger T_Insights_D
on Insights
instead of delete
as
    set nocount on
    delete from broader_insights_insights
    where insight_id in (select ID from deleted) or
    broader_insight_id in (select ID from deleted)

    delete from Insights where ID in (select ID from deleted)

---

Frequently with cascading deletes and lots of foreign keys, you need to spend time to work out a "cascade" order so that the delete that occurs at the top of a "tree" is successfully cascaded to referencing tables. But that isn't possible in this case.

通常使用级联删除和大量外键，您需要花时间计算“级联”顺序，以便在“树”顶部发生的删除成功级联到引用表。但在这种情况下，这是不可能的。