SQL Server 2005 - 级联删除的外键 [英] SQL Server 2005 - Foreign Keys with Cascaded Delete

问题描述

在SQL Server 2005中有没有办法解决这个问题？

（它让我感到困扰，每次遇到它，我都会陷入困境。但这是我第一次不得不面对和处理Stack ）

维度表：

id INT IDENTITY（1,1 ）

FactTable：

source_id INT NOT NULL，

target_id INT NOT NULL p>

我创建了一个从Fact.source_id到Dimension.id的外键，并通过级联删除和级联更新进行设置。

当我试图用Fact.target_id来做同样的事情时，我得到一个错误，告诉哦，不，不，不，因为有多条路径可以跟踪。

这里没有涉及其他外键，只是我想要创建的两个外键。我可以创建没有级联的第二个外键，但对我来说这似乎很流行。这是一套手工维护的桌面（很少会被触摸，所以客户不会为此付钱）。这样一个级联删除/更新将是非常有用的，但只有如果它在两个领域。 （我是一致性的吸盘）

****呜咽****

解决方案

是的，使用INSTEAD OF触发器。它将拦截DELETE命令，并可以相应地指示逻辑。

Is there a way around this in SQL Server 2005?

(It bugs me, and every time I encounter it I get in to a strop. But this is the first time I've had to deal with AND been on Stack Overflow. Please, save what little sanity I posess!)

DimensionTable:
id INT IDENTITY(1,1)

FactTable:
source_id INT NOT NULL,
target_id INT NOT NULL

I created a foreign key from "Fact.source_id" to "Dimension.id" and set it up with cascade delete and cascade update.

When I tried to do the same with "Fact.target_id" I got an error telling "oh no, no, no" as there are multiple paths for the cascade to follow.

There are no other foreign keys involved here, just the two I want to create. I can create the second foreign key Without the Cascade, but that seems quite poo to me. It's a set of tables which are going to be maintained by hand (very rarely will they be touched, so the client won't pay for a gui to do this.) A such a cascade delete/update would be Extremely useful, but only if it' on Both fields. (I'm a sucker for consistency)

****whimper****

解决方案

Yes, use an INSTEAD OF trigger. It'll intercept the DELETE command and you can direct logic accordingly.

这篇关于SQL Server 2005 - 级联删除的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！