Multiple FKs with ON DELETE CASCADE to the same table (MSSQL2008)

views:

406

answers:

Multiple FKs with ON DELETE CASCADE to the same table (MSSQL2008)

I'm running into the problem described by KB321843 and this question. Both only talk about MS SQL Server 2005 (or older), while I'm running 2008.

I'd really had hope that this is fixed in recent versions, but it doesn't seem like it is. Could someone confirm this (or, better tell me how I could get it to work)?

It can't be fixed.

What if you specify one cascade to a table as NULL, another is DELETE. How can this be resolved?

I've hit the same error, but I decided to use triggers mostly so I control the deletes.

gbn 2009-07-09 17:49:37

The semantics I want are very easy: I have a N:M self-reference in a separate table. This table has two FKs to the original table and if I delete one of the entries in the "main" table, I do not need the connections to other items any more. Even in the mixed case of NULL and DELETE a straight-forward implementation would be possible. Postgres 8.1 for example has absolutely no problem with this use case and TOTALLY works as expected.

David Schmitt 2009-07-09 18:03:15

What does Postgres do?

gbn 2009-07-09 18:40:39

Given a connection table (X,Y) where X specifies ON DELETE CASCADE and Y specifies ON DELETE SET NULL and contains the tuples (3,2) and (2,3). After deleting the tuple with id=3 in the referenced table, the connection table contains (2, NULL). What else should it do?!

David Schmitt 2009-07-10 09:53:20

Remove (3,2), it should try and do both: delete tuple (2, 3) because of ON DELETE CASCADE and change it to (2, NULL) because of ON DELETE SET NULL = conflict. And what about "delete where id < 4"?

gbn 2009-07-10 11:10:42

ON DELETE is an attribute of the REFERENCES clause. Thus the implementation knows exactly which FK should be affected. Also I see no conflict when setting one of the FKs to NULL and (then) deleting the tuple due to the other.

David Schmitt 2009-08-26 08:06:27

We'll just have to disagree. My example of "delete where id < 4" clinches it for me.

gbn 2009-08-26 08:28:35

ansaurus

tags:

views:

answers:

Multiple FKs with ON DELETE CASCADE to the same table (MSSQL2008)

related questions