MS SQL“ON DELETE CASCADE”多个外键指向同一个表？ [英] MS SQL "ON DELETE CASCADE" multiple foreign keys pointing to the same table?

问题描述

Howdy，
我有一个问题，我需要一个级联多个外键指向同一个表..

  [Insights] 
 | ID |标题| 
 | 1 | Monty Python | 
 | 2 | Spamalot | 
 
 [BroaderInsights_Insights] 
 | broaderinsight_id | insight_id | 
 | 1 | 2 | 
 

基本上当Insights表中的一条或两条记录被删除时，我需要关系也被删除..

我试过这个：

  CREATE TABLE bigger_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 
  

这会导致警告，级联引起循环或多个级联路径

So ive尝试添加一个级联到insight_id，结果是：

DELETE语句与REFERENCE约束冲突

任何想法？

Daniel

解决方案

您必须将其实现为INSTEAD OF删除触发器洞察力，使其工作。例如：

 在Insights上创建触发器T_Insights_D 
 
而不是delete 
 as 
 set nocount on 
从broader_insights_insights中删除
其中insight_id（选择已删除的ID）或
 broader_insight_id（选择已删除的ID）
 
从Insights中删除where ID in（select ID from deleted）
  

---

级联删除和大量外键，您需要花时间制定一个级联顺序，以便在树顶部发生的删除成功地级联到引用表。但在这种情况下是不可能的。

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:

"The DELETE statement conflicted with the REFERENCE constraint"

Any ideas?

Thanks

Daniel

解决方案

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

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.

这篇关于MS SQL“ON DELETE CASCADE”多个外键指向同一个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！