SQL Server 2005 + Enforce Foreign Key Constraint + Cacade Delete

Question

I am using SQL Server 2005 and I have to relationships going into one table. I had to turn off " Enforce Foreign Key Constraints" because I have 2 relationships going into the same table.

However I want to put cascade delete on.

I thought if I have cascade delete on both of these relationships and if I say deleted something from on of these tables it would cascade and delete into the other table.

However it does not seem to work that way and I am wondering is it because I have the foriegn key constraint off?

If this is the case how can I get around this?

Answer 1

You've gotta have a fk constraint to enforce cascade delete. how sql server know what to delete otherwise?

Answer 2

I'm not clear on why you needed to disable the foreign key constraints in the first place. You can have many relationships to the same table that all enforce referential integrity. However, if you have two relations to the same parent table in the same child table, you can only have cascade update or cascade delete enabled on one of them.

TBH, I cannot think of a situation where I would want a relationship but wouldn't want it enforced. You should always fix the data and enforce the relation so that the data cannot get corrupted.

This is actually a situation where funneling data access through stored procedures helps. If you forced people to only delete through a stored procedure, you could enforce the cascade delete in the procedure without having to enforce in the DRI.