CODEWITHSUNDEEP
c#sqlsql-server-2005cascading-deletes
Rufus
Rufus

Reputation: 121

SQL DELETE (Cascading DELETE vs. TRIGGERS vs. "Manual"-DELETE)

I'm currently working with SQL Server Management Studio 2005 and I'm facing a bit of a problem, but first an extract (the important one) of my DB-schema:

imghack link to the image

I want to delete from the test table and everything it is connected to (is composed of).

The problem is, I can't alter the DB anymore (and really wouldn't want to, cause the program using it got too big already).

When I try to delete an entry in the test table and cascading DELETE is turned on for the corresponding foreign key relation the DBMS won't let me do the delete, because it detected some kind of loop (probably caused by the contr_tray table).

When trying to delete an entry manually through my program (using a SqlCommandBuilder or cmd.ExecuteNonQuery()) I get this error:

The DELETE statement conflicted with the REFERENCE constraint ...

The same happens if I use triggers, to "cascade" the deletion process downwards.

Can anyone help me here? Thank you!

P.S.: I also tried to create a joined table where all columns belonging to the subset table would simply be NULL where the contr_tray table had been joined to the replicate table and let the SqlCommandBuilder handle this, but my SQL skills don't seem up to par (i.e. i didn't get the SQL syntax right)[then I could also get the distinct values for all the IDs I would need ...]

Any help would be appreciated and again thanks in advance.

Upvotes: 2

Views: 1137

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52137

The SQL Server (annoyingly!) doesn't support ON CASCADE DELETE on diamond-shaped dependencies. In your case, Block is at the bottom of the "diamond" so SQL Server is unable to automatically cascade the parent deletes towards it.

Since you are not using identifying relationships, you'd need to first SELECT top-down to find all the IDs to be deleted and then perform the actual DELETE bottom-up (to avoid violating FKs in the process).

You should be able encapsulate this logic in a series of INSTEAD OF DELETE triggers - what exactly is the problem with your triggers?

Upvotes: 1

Jeff S
Jeff S

Reputation: 7484

The error you provide isn't related to cascading deletes. It is telling you that a row can't be deleted because it is being referenced by a foreign key. This will occur when you don't have a cascaded delete all the way from Test to Well including all of the relationships in between.

If this isn't the answer you were expecting, we need more information on how the relationships are set up between each of the tables.

Upvotes: 0

Related Questions