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

我花了很多时间试图找出如何实现删除时级联SQL Server 上的递归主键已经有一段时间了。我已经阅读了有关触发器、创建临时表等的内容，但尚未找到适合我的数据库设计的答案。

这是一个用于演示目的的老板/员工数据库示例：

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。

例如，如果 Rick 被解雇，我们会这样做：

DELETE FROM employee WHERE id=7

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

现在，假设我们已经度过了困难时期，我们需要放弃汉密尔顿和他的entire部门。这意味着我们需要删除

• 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，并且它的行为完全符合我的预期。再说一遍，我want如果删除了父母、祖父母、曾祖父母等，则将删除父母的每个可能的孩子、孙子女、曾孙子女等。

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

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

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

• 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
  • 因为你并不真正需要删除级联，因为你总是有时间随时随地更改你和所有其他人的所有代码（例如与其他系统的接口），从而删除一个员工（或多个员工，注意：复数）（包括此数据库中的所有上级和从属对象[包括添加一个或多个新对象]（以及其他客户的此数据库的任何其他副本，特别是在生产中，当您无权访问数据库时[哦，并且关于测试系统、集成系统以及生产、测试​​和集成的本地副本]

or

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

PS:
那是讽刺。

Note:

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

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

;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