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.

Solution 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).

Solution 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

Comments

[Damien_The_Unbeliever]

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?

[Andomar]

@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.

[Damien_The_Unbeliever]

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

[Andomar]

@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.

[Tim Schmelter]

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".

[Tim Schmelter]

@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.

[Tim Schmelter]

@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?

[Andomar]

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

[Arif]

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.

[Damien_The_Unbeliever]

@Arif - 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.

[Arif]

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

[Daniel Rodríguez]

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

[Daniel Rodríguez]

@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