SQL Server - 递归外键级联删除

Question

我已经花了相当多的时间来研究如何在SQL Server上实现针对递归主键的CASCADE ON DELETE。我阅读了有关触发器、创建临时表等方面的内容，但是还没有找到可适用于我的数据库设计的答案。

下面是一个老板/员工数据库示例，供演示使用：

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。

举个例子，如果Rick被解雇了，我们会这样做:

DELETE FROM employee WHERE id=7

这应该从员工和信息表中删除Rick的行。太好了，级联操作！

现在，假设我们遇到了困难时期，需要解雇Hamilton及其整个部门。这意味着我们需要在运行以下命令时从员工和信息表中移除：

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

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），并且它完全按照我的预期运行。同样的，我希望在父对象被删除时删除其所有可能的子对象、孙子对象、曾孙对象等。

当我尝试使用触发器时，我似乎无法触发它本身（例如，在尝试删除Hamilton的员工Susan之前，先查看Susan是否有任何员工等），更不用说向下进行N个员工的删除了。

所以！我认为我已经提供了我能想到的每一个细节。如果还有什么不清楚的地方，我会努力改进这个描述。

- J. Colby Fisher

Answer 1

死灵术。

这里有两个简单的解决方案。

• 你可以阅读微软的道歉（因为它很困难且耗时 - 时间就是金钱），以及它们没有实现递归级联删除的解释，和为什么你不需要/不应该使用它（尽管你需要），然后在存储过程中使用游标实现删除功能,
  • 因为你不真正需要级联删除，因为你总是有时间在任何地方随时更改所有您和其他人的代码（如与其他系统的接口）来删除此数据库中的员工（或员工，注意：复数）（包括所有上级和下级对象[包括添加一个或多个新对象时]）。在此数据库中删除此数据库中的员工（和任何其他客户的此数据库副本，特别是在您无法访问数据库时生产环境中的副本[噢，以及在测试系统、集成系统和生产、测试和集成的本地副本]

或者

• 你可以使用一个真正支持递归级联删除的适当的DBMS，比如PostGreSQL（只要图形是有向的且非循环; 否则在删除时错误）。

P.S.：那是讽刺。

---

注意：

只要您的删除不是源自级联，而且您只想在自引用表上执行删除，则可以删除任何条目，只要您在in子句中也删除所有下级对象即可。

因此，要删除这样的对象，请执行以下操作：

;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)

假设您有以下表结构：

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

- Stefan Steiger

---

开玩笑的，微软真的为此做了抱歉的借口吗？有任何参考资料吗？ - Tyson

Answer 2

下面的内容可能适合您（我没有测试过，因此可能需要进行一些调整）。似乎只需在删除上层员工之前从层次结构底部删除员工即可。使用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

- Kevin Suchlicki

---

在修复了一些语法问题之后，我可以确认这个过程确实做到了我想要的。非常感谢！ - J. Colby Fisher

Answer 3

这可能听起来有些极端，但我认为没有一个简单的内置选项可以满足您的需求。我建议创建一个存储过程来执行以下操作：

1. 禁用FK约束
2. 使用递归CTE获取要删除的员工列表（将其保存在临时表中）
3. 从父/子表中删除行
4. 从员工信息表中删除行
5. 启用FK约束

将整个过程包装在事务中以保持一致性。

- Mark Wojciechowicz

---

如果您使用的是“Delete from employee WHERE id in (/某些CTE表达式/)"语句，则无需禁用外键约束。SQL Server将检查所有引用是否在已删除的对象内，并确认其合法性。 - Bogdan Mart