MS SQL“删除级联"多个外键指向同一张表?

问题描述

我有一个问题，我需要对指向同一个表的多个外键进行级联..

[洞察]|身份证 |标题 ||1 |巨蟒 ||2 |斯帕马洛特 |[BroaderInsights_Insights]|更广泛的见解_id |洞察ID ||1 |2 |

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

我试过了:

 CREATE TABLE broad_insights_insights(id INT NOT NULL IDENTITY(1,1),broad_insight_id INT NOT NULL REFERENCESInsights(id) ON DELETE CASCADE,insight_id INT NOT NULL REFERENCESInsights(id) ON DELETE CASCADE,主键(id))去

这会导致警告说级联可能导致循环或多级联路径"

所以我尝试将级联添加到insight_id，结果是:

DELETE 语句与 REFERENCE 约束冲突

有什么想法吗?

谢谢

丹尼尔

解决方案

您必须将此作为 INSTEAD OF 删除触发器来实现洞察力，才能使其正常工作.比如:

创建触发器 T_Insights_D洞察力而不是删除作为设置不计数从更广泛的见解中删除其中insight_id in(从已删除中选择ID)或broad_insight_id in(从已删除中选择 ID)从 ID 所在的 Insights 中删除(从已删除中选择 ID)

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

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

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

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“删除级联"多个外键指向同一张表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！