title: Two foreign keys on same table: how to implement on delete cascade?
body: <p>I have a table with two columns. Each of them is a foreign key to the same second table:</p>
  
  <pre><code>CREATE TABLE [dbo].[TBL_TOGETHER]
  (
      [ID1] [int] NULL FOREIGN KEY REFERENCES [TBL_ANOTHER_TABLE](ID),
      [ID2] [int] NULL FOREIGN KEY REFERENCES [TBL_ANOTHER_TABLE](ID)
  )
  </code></pre>
  
  <p>But now I can not delete entities from <code>TBL_ANOTHER_TABLE</code> anymore:</p>
  
  <blockquote>
    <p>The DELETE statement conflicted with the REFERENCE constraint "FK__TBL_ASD__4DE98D56".</p>
  </blockquote>
  
  <p>My question is: what is the best solution to implement <code>ON DELETE CASCADE</code> at <code>TBL_TOGETHER</code>?</p>
  
  <p>I tried to add <code>ON DELETE SET NULL</code> or <code>ON DELETE CASCADE</code> on the foreign key, but it does not work because of cycle or multiple cascade paths.</p>
  
  <p>I have tried to add a delete trigger on <code>TBL_ANOTEHR_TABLE</code>, but it still conflicts with the foreign key:</p>
  
  <pre><code>ALTER TRIGGER REMOVE_FORENGKEY
  ON TBL_ANOTHER_TABLE 
  FOR DELETE
  AS
  BEGIN
      UPDATE TBL_TOGETHER
      SET TBL_TOGETHER.ID1 = NULL
      FROM TBL_TOGETHER
      JOIN deleted ON TBL_TOGETHER.ID1 = deleted.ID;
  
      UPDATE TBL_TOGETHER
      SET TBL_TOGETHER.ID2 = NULL
      FROM TBL_TOGETHER
      JOIN deleted ON TBL_TOGETHER.ID2 = deleted.ID;
  END
  </code></pre>
  
  <p>I have tried an <code>INSTEAD OF DELETE</code>-trigger but this approach also doesn't work because the referenced table ALSO contains a foreign key with cascade constraints. </p>
  
  <pre><code>ALTER TRIGGER REMOVE_FORENGKEY
  ON TBL_ANOTHER_TABLE 
  INSTEAD OF DELETE
  AS
  BEGIN
      UPDATE TBL_TOGETHER
      SET TBL_TOGETHER.ID1 = NULL
      FROM TBL_TOGETHER
      JOIN deleted ON TBL_TOGETHER.ID1 = deleted.ID;
  
      UPDATE TBL_TOGETHER
      SET TBL_TOGETHER.ID2 = NULL
      FROM TBL_TOGETHER
      JOIN deleted ON TBL_TOGETHER.ID2 = deleted.ID;
  
      DELETE TBL_ANOTHER_TABLE 
      FROM TBL_ANOTHER_TABLE 
      JOIN deleted ON TBL_ANOTHER_TABLE.ID = deleted.ID 
      WHERE TBL_ANOTHER_TABLE.ID = deleted.ID;
  END
  </code></pre>
  
  <p>Error:</p>
  
  <blockquote>
    <p>Cannot alter INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER 'REMOVE_FORENGKEY' on table 'TBL_ANOTHER_TABLE '. This is  because the table has a FOREIGN KEY with cascading DELETE or UPDATE.</p>
  </blockquote>