SQL Server - 使用递归外键级联删除 [英] SQL Server - Cascading DELETE with Recursive Foreign Keys

问题描述

我花了很多时间试图找出如何在SQL Server上为递归主键实现 CASCADE ON DELETE 一段时间。我已经阅读了关于触发器，创建临时表等，但尚未找到一个将与我的数据库设计工作的答案。

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.

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

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

正如你所看到的，每个员工都有一个老板，也是一个员工。因此，id / boss_id上有一个PK / FK关系。

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

在employee.id/information.emp_id中有一个PK / FK，其中有一个CASCADE ON DELETE。

There is a PK/FK on employee.id/information.emp_id with a CASCADE ON DELETE.

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

For example, if Rick was fired, we would do this:

DELETE FROM employee WHERE id=7

这应该从雇员和信息中删除Rick的行。 Yay cascade！

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

• 汉密尔顿


• Scott


• 苏珊


• 塞思


• Rick


• Rachael

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

当我们运行时，从员工和信息表中：

From both the employee and information tables when we run:

DELETE FROM employee WHERE id=3

我尝试了一个简单的CASCADE ON DELETE id / emp_id，没有它：

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.

我可以在Access中的测试数据库上使用CASCADE ON DELETE，它的行为与I想要它。再次，如果父项，父项，曾祖父项等的父项，子项，曾祖父项等都被删除，我<希望删除父项的每个可能的子项，孙子，孙子等。

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.

当我尝试使用触发器，我似乎无法触发自己（例如，当你尝试删除汉密尔顿的员工苏珊，首先看看苏珊是否有任何员工等）更不用说N个员工。

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.

推荐答案

以下内容可能适用于您没有测试它，所以它可能需要一些调整）。看起来像你所要做的就是删除层次结构底部的员工，然后再删除高层。使用CTE递归构建删除层次，并将CTE输出按员工的层次结构级别降序排列。然后按顺序删除。

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

这篇关于SQL Server - 使用递归外键级联删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！