MS SQL“ON DELETE CASCADE”多个外键指向同一个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5018099/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MS SQL "ON DELETE CASCADE" multiple foreign keys pointing to the same table?
提问by Daniel Upton
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
丹尼尔
回答by Damien_The_Unbeliever
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.
经常使用级联删除和大量外键，您需要花时间制定“级联”顺序，以便发生在“树”顶部的删除成功级联到引用表。但在这种情况下这是不可能的。
