SQL Server 2005 - Foreigh Keys with Cascaded Delete

Question

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**

Answer 1

You can only cascade delete from one relationship. Suggest you set this up in triggers intead.

Answer 2

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