title: Self referencing foreign-key constraints and delete
body: <p>what is the recommended way to handle self-referencing foreignkey constraints in SQL-Server?</p>
  
  <p>Table-Model:</p>
  
  <p><img src="https://i.stack.imgur.com/MHdmX.jpg" alt="enter image description here"></p>
  
  <p><code>fiData</code> references a previous record in tabData. If i delete a record that is referenced by <code>fiData</code>, the database throws an exception: </p>
  
  <blockquote>
    <p>"The DELETE statement conflicted with the SAME TABLE REFERENCE
    constraint "FK_tabDataPrev_tabDataNext". The conflict occurred in
    database "MyDataBase", table "dbo.tabData", column 'fiData'"</p>
  </blockquote>
  
  <p>if <code>Enforce Foreignkey Constraint</code> is set to "Yes".</p>
  
  <p>I don't need to cascade delete records that are referenced but i would need to set <code>fiData=NULL</code> where it's referenced. My idea is to set <code>Enforce Foreignkey Constraint</code> to "No" and create a delete-trigger. Is this recommendable or are there better ways?</p>
  
  <p>Thank you.</p>