Tim Schmelter Tim Schmelter - 4 years ago 154
SQL Question

Self referencing foreign-key constraints and delete

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

Table-Model:

enter image description here

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.

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download