SQL Server-使用递归外键级联删除

Question

我花了很多时间试图弄清楚如何在一段时间内为SQL Server上的递归主键实现 CASCADE ON DELETE
。我已经阅读了有关触发器，创建临时表等的信息，但是还没有找到适合我的数据库设计的答案。

这是一个Boss / Employee数据库示例，将用于演示目的：

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

如您所见，每个员工都有一个老板，同时也是一个员工。因此，在id / boss_id上存在PK / FK关系。

这是带有其信息的（缩写）表：

TABLE information
emp_id|street     |phone
------|-----------|-----
2     |blah blah  |blah
6     |blah blah  |blah
7     |blah blah  |blah

在employee.id/information.emp_id上有一个PK / FK，并且有CASCADE ON DELETE。

例如，如果瑞克被解雇，我们可以这样做：

DELETE FROM employee WHERE id=7

这应该从员工和信息中删除Rick的行。耶级联！

现在，说我们经历了艰难的时期，我们需要解雇汉密尔顿及其 整个 部门。这意味着我们将需要删除

• 汉密尔顿
• 史考特
• 苏珊
• 赛斯
• 里克
• 雷切尔

当我们运行时，从employee和information表中：

DELETE FROM employee WHERE id=3

我为id / emp_id尝试了一个简单的CASCADE ON DELETE，但是SQL Server没有：

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.

我可以在Access中的测试数据库上使用CASCADE ON DELETE，它的行为完全符合我的期望。再次，我 希望
删除父母的所有可能的孩子，孙子，曾孙等，如果他们的父母，祖父母，曾祖父母等被删除。

当我尝试使用触发器时，我似乎无法使其触发自身（例如，当您尝试删除汉密尔顿的雇员苏珊，首先查看苏珊是否有任何雇员，等等），更不用说减少N个雇员了。

所以！我想我已经提供了我能想到的所有细节。如果仍然不清楚，我将尝试改进此描述。

Answer 1

以下内容可能对您有用（我尚未测试过，因此可能需要进行一些调整）。似乎您要做的就是从层次结构的底部删除雇员，然后再删除较高的雇员。使用CTE递归地构建删除层次结构，并按雇员的层次结构级别排列CTE输出降序。然后按顺序删除。

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