title: SQL Server - Cascading DELETE with Recursive Foreign Keys
body: <p>I've spent a good amount of time trying to figure out how to implement a <strong>CASCADE ON DELETE</strong> for recursive primary keys on SQL Server for some time now. I've read about triggers, creating temporary tables, etc but have yet to find an answer that will work with my database design.</p>
  
  <p>Here is a Boss/Employee database example that will work for demonstration purposes:</p>
  
  <pre><code>TABLE employee
  id|name     |boss_id
  --|---------|-------
  1 |John     |1
  2 |Hillary  |1
  3 |Hamilton |1
  4 |Scott    |2
  5 |Susan    |2
  6 |Seth     |2
  7 |Rick     |5
  8 |Rachael  |5
  </code></pre>
  
  <p>As you can see, each employee has a boss that is also an employee. So, there is a PK/FK relationship on id/boss_id.</p>
  
  <p>Here is an (abbreviated) table with their information:</p>
  
  <pre><code>TABLE information
  emp_id|street     |phone
  ------|-----------|-----
  2     |blah blah  |blah
  6     |blah blah  |blah
  7     |blah blah  |blah
  </code></pre>
  
  <p>There is a PK/FK on employee.id/information.emp_id with a CASCADE ON DELETE.</p>
  
  <p>For example, if Rick was fired, we would do this:</p>
  
  <pre><code>DELETE FROM employee WHERE id=7
  </code></pre>
  
  <p>This should delete Rick's rows from both employee and information. Yay cascade!</p>
  
  <p>Now, say we've hit hard times and we need to lay of Hamilton and his <strong>entire</strong> department. This means that we would need to remove</p>
  
  <ul>
  <li>Hamilton</li>
  <li>Scott</li>
  <li>Susan</li>
  <li>Seth</li>
  <li>Rick</li>
  <li>Rachael</li>
  </ul>
  
  <p>From both the employee and information tables when we run:</p>
  
  <pre><code>DELETE FROM employee WHERE id=3
  </code></pre>
  
  <p>I tried a simple CASCADE ON DELETE for id/emp_id, but SQL Server wasn't having it:</p>
  
  <pre><code>Introducing FOREIGN KEY constraint 'fk_boss_employee' on table 'employee' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
  </code></pre>
  
  <p>I was able to use CASCADE ON DELETE on a test database in Access, and it behaved exactly as I wanted it to. Again, I <strong>want</strong> every possible child, grandchild, great-grandchild, etc of a parent to be deleted if their parent, grandparent, great-grandparent, etc is deleted.</p>
  
  <p>When I tried using triggers, I couldn't seem to get it to trigger itself (eg. when you try to delete Hamilton's employee Susan, first see if Susan has any employees, etc) let alone going down N-number of employees.</p>
  
  <p>So! I think I've provided every detail I can think of. If something still isn't clear, I'll try to improve this description.</p>