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.
MySQL supports foreign key references between one column and another within a table. (A column cannot have a foreign key reference to itself.) In these cases, a “child table record” refers to a dependent record within the same table.
To drop a foreign key from a table, use the ALTER TABLE clause with the name of the table (in our example, student ) followed by the clause DROP CONSTRAINT with the name of the foreign key constraint.
A self-referencing constraint exists if a Db2® object is subject to a primary or foreign key relationship in which the parent table and the dependent table are the same table. If the DELETE rule for the relationship is CASCADE, the deletion or change of one row can cause a recursive deletion of other rows in the table.
When a referenced foreign key is deleted or updated, respectively, the columns of all rows referencing that key will be set to NULL . The column must allow NULL or this update will fail.
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With