我有一个问题,我需要在指向同一个表的多个外键上进行级联。
[Insights]
| ID | Title |
| 1 | Monty Python |
| 2 | Spamalot |
[BroaderInsights_Insights]
| broaderinsight_id | insight_id |
| 1 | 2 |
基本上,当insights表中的一条或两条记录被删除时,我也需要删除关系。
我试过了:
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
这会导致级联“可能导致循环或多个级联路径”的警告
因此,我尝试只向insight_id添加一个级联,结果是:
DELETE语句与引用约束冲突
有什么想法吗?
谢谢
丹尼尔
发布于 2011-02-16 23:18:27
您必须将其实现为insights上的INSTEAD OF delete触发器,才能使其正常工作。类似于:
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)
通常,对于级联删除和大量外键,您需要花费时间来确定“级联”顺序,以便将“树”顶部发生的删除成功级联到引用表。但在这种情况下,这是不可能的。
https://stackoverflow.com/questions/5018099
复制相似问题