SQL Server - 使用递归外键进行级联 DELETE

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 的行。耶级联!

现在，假设我们遇到了困难时期，我们需要放弃汉密尔顿和他的整个部门。这意味着我们需要删除

• 汉密尔顿
• 斯科特
• 苏珊
• 赛斯
• 瑞克
• 雷切尔

当我们运行时，来自员工和信息表:

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，并且它的行为完全符合我的预期。再次，如果删除了 parent 、祖 parent 、曾祖 parent 等，我希望删除 parent 的每个可能的 child 、孙子、曾孙等。

当我尝试使用触发器时，我似乎无法让它自行触发(例如，当您尝试删除 Hamilton 的员工 Susan 时，首先查看 Susan 是否有任何员工等)，更不用说下降 N 次了员工。

那么!我想我已经提供了我能想到的所有细节。如果还有不清楚的地方，我会尝试改进此描述。

Answer 1

死灵魔法。
有两个简单的解决方案。

• 您可以阅读 Microsoft 的抱歉借口，了解为什么他们没有这样做实现这个(因为它很困难且耗时 - 时间就是金钱)，并解释为什么你不需要/不应该需要它(尽管你需要)，并使用存储的游标实现删除功能程序
  • 因为您并不真正需要删除级联，因为您总是有时间随时随地更改您和所有其他人的所有代码(例如与其他系统的接口(interface))，从而删除一个或多个员工，请注意:复数)(包括所有上级和下级对象[包括添加一个或多个新对象时])在此数据库(以及其他客户的此数据库的任何其他副本，特别是在生产中，当您无权访问该数据库时[哦，还有测试系统、集成系统、生产、测试​​和集成的本地副本]

或

• 您可以使用实际上支持递归级联删除的适当 DBMS，例如 PostGreSQL(只要图是有向的且非循环的；否则删除时会出现错误)。

PS:
这是讽刺。

<小时/>

注意:

只要您的删除不是源于级联，并且您只想在自引用表上执行删除，您就可以删除任何条目，只要您同时删除内部对象中的所有从属对象即可条款。

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

;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