Self referencing foreign-key constraints and delete

Question

what is the recommended way to handle self-referencing foreignkey constraints in SQL-Server?

Table-Model:

fiData references a previous record in tabData. If i delete a record that is referenced by fiData, the database throws an exception:

"The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_tabDataPrev_tabDataNext". The conflict occurred in database "MyDataBase", table "dbo.tabData", column 'fiData'"

if Enforce Foreignkey Constraint is set to "Yes".

I don't need to cascade delete records that are referenced but i would need to set fiData=NULL where it's referenced. My idea is to set Enforce Foreignkey Constraint to "No" and create a delete-trigger. Is this recommendable or are there better ways?

Thank you.

Lotte

Answer 1

Unlike Andomar, I'd be happy using a trigger - but I wouldn't remove the constraint checking. If you implement it as an instead of trigger, you can reset the other rows to null before performing the actual delete:

CREATE TRIGGER T_tabData_D
on tabData
instead of delete
as
    set nocount on
    update tabData set fiData = null where fiData in (select idData from deleted)
    delete from tabData where idData in (select idData from deleted)

It's short, it's succinct, it wouldn't be necessary if SQL Server could handle foreign key cascades to the same table (in other RDBMS', you may be able to just specify ON DELETE SET NULL for the foreign key constraint, YMMV).

Immaculate

Thanks. But i get an exception on creating the trigger: "INSTEAD OF DELETE/UPDATE triggers cannot be defined on a table that has a foreign key with a cascade on DELETE/UPDATE action defined". – Lotte

I know this too late to comment but for someone who is searching like me. This will only delete one entry. it will not cascade recursively. – Reprint

@Reprint - this was "ON DELETE SET NULL", not "ON DELETE CASCADE". The former never needs to recurse. For "ON DELETE CASCADE", I'd recommend a CTE that computes the closure over all ID values first, then performs the delete. – Immaculate

i didn't noticed this in question. but first comment says "cascade on DELETE/UPDATE" commented by the person who questioned. – Reprint

@TimSchmelter I got blocked at the same point. I think the solution could be to remove the other CASCADE actions from the table and then add the equivalent logic in the Trigger... Still didn't find any better way to do it. Please tell in case you found one – Lamarlamarck

Answer 2

Triggers add implicit complexity. In a database with triggers, you won't know what a SQL statement does by looking at it. In my experience triggers are a bad idea with no exceptions.

In your example, setting the enforced constrained to "No" means you could add a nonexistent ID. And the query optimizer will be less effective because it can't assume the key is valid.

Consider creating a stored procedure instead:

create procedure dbo.NukeTabData(
    @idData int)
as
begin transaction
update tabData set fiData = null where fiData = @idData
delete from tabData where idData = @idData
commit transaction
go

Wieren

Now your calling code has exec NukeTabData(ID), and you have no idea what that does without examining the contents of the procedure. Why is that okay, when a trigger isn't? – Immaculate

@Damien_The_Unbeliever: The stored procedure is explicit: it tells me where I should look if I'm interested in the details. A trigger is implicit: nothing in delete from tabData where idData = 42 hints at the presence of additional updates that will run. For example, after running the delete, a trigger could cause @@rowcount to be 4 instead of 1. This results in difficult bugs-- especially for developers who are new to the code base. – Wieren

@@ROWCOUNT is scope based - it's not affected by any activity within the trigger. You're possibly thinking of @@IDENTITY, but most people know to avoid that these days – Immaculate

@Damien_The_Unbeliever: Well there's a whole class of side effects: @@identity, possibility of rollback, unexpected deadlocks. A common mistake seems to be audits or error logging using a trigger. When the transaction is rolled back, the logging is rolled back too. So when a new order fails and is rolled back, it doesn't show up in the error logs, and it looks like no order was placed at all. – Wieren

@Andomar: Thanks. But the table is already involved in several applications and services where each could change/delete records. This would'nt be worthwile to change all using the stored-procedure instead. – Lotte

@Andomar: i ended with using what i've already mentioned, "enforce fk-constraint=No" and a after delete-trigger that sets fiData to NULL. Can you explain what you mean with "the query optimizer will be less effective because it can't assume the key is valid"? The table has already ~12millionen rows and is freuqently queried. fiData will never be added manually but calculated by a stored-proc every morning. Hence i can ensure that the fk is valid. How to tell that SQL-Server? – Lotte

@Tim Schmelter: A foreign key is the only effective way, but it shouldn't matter too much, usually just a micro optimization. – Wieren

Answer 3

This very late to answer.

But for some one who is searching like me.

and want to cascade

here is very good explanation

http://devio.wordpress.com/2008/05/23/recursive-delete-in-sql-server/

The Problem Although you can define a foreign key with CASCADE DELETE in SQL Server, recursive cascading deletes are not supported (i.e. cascading delete on the same table).

If you create an INSTEAD OF DELETE trigger, this trigger only fires for the first DELETE statement, and does not fire for records recursively deleted from this trigger.

This behavior is documented on MSDN for SQL Server 2000 and SQL Server 2005.

The Solution Suppose you have a table defined like this:

CREATE TABLE MyTable (
    OID    INT,        -- primary key
    OID_Parent INT,    -- recursion
    ... other columns
)

then the delete trigger looks like this:

CREATE TRIGGER del_MyTable ON MyTable INSTEAD OF DELETE
AS
    CREATE TABLE #Table(
        OID    INT
    )
INSERT INTO #Table (OID)
SELECT  OID
FROM    deleted

DECLARE @c INT
SET @c = 0

WHILE @c <> (SELECT COUNT(OID) FROM #Table) BEGIN
    SELECT @c = COUNT(OID) FROM #Table

    INSERT INTO #Table (OID)
    SELECT  MyTable.OID
    FROM    MyTable
    LEFT OUTER JOIN #Table ON MyTable.OID = #Table.OID
    WHERE   MyTable.OID_Parent IN (SELECT OID FROM #Table)
    AND     #Table.OID IS NULL
END

DELETE  MyTable
FROM    MyTable
INNER JOIN #Table ON MyTable.OID = #Table.OID

GO

Reprint

This is a good answer for ON DELETE CASCADE, but the question is about ON DELETE SET NULL – Lamarlamarck