您必须将此作为 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

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.