SQL Server - Cascading DELETE with Recursive Foreign Keys

Question

I've spent a good amount of time trying to figure out how to implement a CASCADE ON DELETE 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. Here is a Boss/Employee database example that will work for demonstration purposes: ``` 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 ``` 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. Here is an (abbreviated) table with their information: ``` TABLE information emp_id|street |phone ------|-----------|----- 2 |blah blah |blah 6 |blah blah |blah 7 |blah blah |blah ``` There is a PK/FK on employee.id/information.emp_id with a CASCADE ON DELETE. For example, if Rick was fired, we would do this: ``` DELETE FROM employee WHERE id=7 ``` This sho…

Accepted Answer

The below might work for you (I haven't tested it so it may require some tweaking). Seems like all you have to do is delete the employees from the bottom of the hierarchy before you delete the ones higher-up. Use a CTE to build the delete hierarchy recursively and order the CTE output descending by the hierarchy level of the employee. Then delete in order.

```
CREATE PROC usp_DeleteEmployeeAndSubordinates (@empId INT)
AS

;WITH employeesToDelete AS (
    SELECT  id, CAST(1 AS INT) AS empLevel
    FROM    employee
    WHERE   id = @empId
    UNION ALL
    SELECT  e.id, etd.empLevel + 1
    FROM    employee e
            JOIN employeesToDelete etd ON e.boss_id = etd.id AND e.boss_id != e.id
)
SELECT  id, ROW_NUMBER() OVER (ORDER BY empLevel DESC) Ord
INTO    #employeesToDelete
FROM    employeesToDelete;

DECLARE @current INT = 1, @max INT = @@ROWCOUNT;

WHILE @current <= @max
BEGIN
    DELETE employee WHERE id = (SELECT id FROM #employeesToDelete WHERE Ord = @current);
    SET @current = @current + 1;
END;
GO
```

SQL Server - Cascading DELETE with Recursive Foreign Keys

Solution

Problem