title: Delete rows with parent/child relation in single table from stored procedure
body: <p>I need to suggestion how can be resolve this problem.</p>
  <p>Table is constructed with parent/child relations columns and contains foreign key between this relations.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">Id</th>
  <th style="text-align: center;">ParentId</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">1</td>
  <td style="text-align: center;">1</td>
  </tr>
  <tr>
  <td style="text-align: left;">2</td>
  <td style="text-align: center;">NULL</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>In my stored procedure I added deleting all children in first query and then deleting all parent. All children delete correctly but during parents rows deleting throw error:</p>
  <blockquote>
  <p>The DELETE statement conflicted with the SAME TABLE REFERENCE constraint.</p>
  </blockquote>
  <p>Findings:</p>
  <ol>
  <li><p>Set foreign key with 'ON DELETED CASCADE' will throw exception:</p>
  <blockquote>
  <p>may cause cycles or multiple cascade paths</p>
  </blockquote>
  </li>
  <li><p>Execute below queries in stored procedure will throw exception:</p>
  <blockquote>
  <p>The DELETE statement conflicted with the SAME TABLE REFERENCE constraint</p>
  </blockquote>
  </li>
  <li><p>Inconsistent data in table causes that cannot recreate foreign key</p>
  </li>
  <li><p>I tried to wrap delete query with EXEC inside stored procedure, although it throw same exception like from point 2</p>
  </li>
  </ol>
  <p>How could be this problem resolve without deleting constraint from table or separating executions this queries into different stored procedure?</p>