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 should delete Rick's rows from both employee and information. Yay cascade!

Now, say we've hit hard times and we need to lay of Hamilton and his entire department. This means that we would need to remove

• Hamilton
• Scott
• Susan
• Seth
• Rick
• Rachael

From both the employee and information tables when we run:

DELETE FROM employee WHERE id=3

I tried a simple CASCADE ON DELETE for id/emp_id, but SQL Server wasn't having it:

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.

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 want every possible child, grandchild, great-grandchild, etc of a parent to be deleted if their parent, grandparent, great-grandparent, etc is deleted.

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.

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.

Answer 1

Necromancing.
There's 2 simple solutions.

• You can either read Microsoft's sorry-excuse(s) of why they didn't implement this (because it is difficult and time-consuming - and time is money), and explanation of why you don't/shouldn't need it (although you do), and implement the delete-function with a cursor in a stored procedure
  • because you don't really need delete cascade, because you always have the time to change ALL your and ALL of OTHER people's code (like interfaces to other systems) everywhere, anytime, that deletes an employee (or employees, note: plural) (including all superordinate and subordinate objects [including when a or several new ones are added]) in this database (and any other copies of this database for other customers, especially in production when you don't have access to the database [oh, and on the test system, and the integration system, and local copies of production, test, and integration]

or

• you can use a proper DBMS that actually supports recursive cascaded deletes, like PostGreSQL (as long as the graph is directed, and non-cyclic; else ERROR on delete).

PS:
That's sarcasm.

---

Note:

As long as your delete does not stem from a cascade, and you just want to perform a delete on a self-referencing table, you can delete any entry, as long as you remove all subordinate objects as well in the in-clause.

So to delete such an object, do the following:

;WITH CTE AS 
(
    SELECT id, boss_id, [name] FROM employee
    -- WHERE boss_id IS NULL 
    WHERE id = 2 -- <== this here is the id you want to delete !

    UNION ALL

    SELECT employee.id, employee.boss_id, employee.[name] FROM employee
    INNER JOIN CTE ON CTE.id = employee.boss_id 
)
DELETE FROM employee 
WHERE employee.id IN (SELECT id FROM CTE)

Assuming you have the following table structure:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.employee') AND type in (N'U'))
BEGIN
CREATE TABLE dbo.employee
(
    id int NOT NULL,
    boss_id int NULL,
    [name] varchar(50) NULL,
    CONSTRAINT PK_employee PRIMARY KEY ( id )
); 
END
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.FK_employee_employee') AND boss_id_object_id = OBJECT_ID(N'dbo.employee'))
ALTER TABLE dbo.employee  WITH CHECK ADD  CONSTRAINT FK_employee_employee FOREIGN KEY(boss_id)
REFERENCES dbo.employee (id)
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.FK_employee_employee') AND boss_id_object_id = OBJECT_ID(N'dbo.employee'))
ALTER TABLE dbo.employee CHECK CONSTRAINT FK_employee_employee
GO

Answer 2

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