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.

7
задан Majid Basirati 6 February 2017 в 12:25
поделиться