Document

🚀 个人主页 极客小俊
✍🏻 作者简介：程序猿、设计师、技术分享
🐋 希望大家多多支持, 我们一起学习和进步！
🏅 欢迎评论 ❤️点赞💬评论 📂收藏 📂加关注

什么事多重级联路径

首先我们来了解一下什么事多重级联路径这个概念

比如

表A引用表B，同时表B也引用表A，或者通过多个表形成循环引用

假设我们有两个表：TableA（表A）和 TableB（表B）

TableA 有一个字段A_ID作为主键，以及一个字段B_Ref作为外键，引用 TableB 的 B_ID 字段
TableB 也有一个字段 B_ID 作为主键，以及一个字段 A_Ref 作为外键，引用 TableA 的 A_ID 字段

如图

这样就形成了一个循环引用, TableA表 引用 TableB表，同时 TableB表 也引用 TableA表

如果现在，如果我们尝试删除 TableA表 中的一条记录，由于TableB表中可能有记录通过 A_Ref 字段引用这条记录，数据库会尝试级联删除这些 TableB表 中的记录!

但是，当尝试删除 TableB表中的这些记录时，由于它们通过 B_Ref 字段引用了 TableA表 中的其他记录, 可能也是我们最初想要删除的那条记录的关联记录，也可能是其他记录，那么数据库会再次尝试级联删除这些 TableA表 中的记录!

以此类推，这个过程可能会无限循环下去，导致数据库操作无法完成, 这就是多重循环级联路径的问题!

同样地，如果存在多个表之间的复杂引用关系，也会形成多重循环级联路径，也可能导致类似的问题

就像是TableA表 引用 TableB表，TableB表 引用 TableC表，而 TableC表 又通过某种方式直接或间接引用回 TableA表 或其他表这样的逻辑关系!

所以这里给大家一个建议, 为了避免这种问题，我们平常开发中对数据库设计上就需要谨慎处理这些外键约束和级联操作, 一定要先把数据的逻辑梳理 清楚!

那么如果在这种情况下，SQL Server 2000下就不允许设置级联删除了,之前我们所讲的级联删除可能就不管用了!

应用场景

比如说我们现在有两个表：Employees（员工表）和 Departments（部门表）

多个员工都归属于一个部门，而每个部门都有一个经理，这个经理也是Employees表中的一个员工!

那么这个场景~就很明显了，双表都要建立关系!

这里就有一个问题:

如果我们想要删除一个部门，我们需要确保有些条件:

1. 删除该部门下的所有员工, 因为员工归属于部门)

2. 更新或删除作为该部门经理的员工记录因为经理是员工之一，但他们的特殊角色需要被处理!

然而，如果我们直接删除一个部门，并且该部门的经理仍然存在于Employees表中，我们可能不希望仅仅因为部门被删除就删除这个经理的员工, 相反，我们可能想要将他们的DepartmentID设置为NULL来这样处理!

那么首先，我们先把数据表创建出来

如下

CREATE TABLE Departments (
    DepartmentID int PRIMARY KEY,
    DepartmentName nvarchar(100),
    ManagerID int, -- 这里存储经理的EmployeeID
    FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)
);

CREATE TABLE Employees (
    EmployeeID int PRIMARY KEY,
    EmployeeName nvarchar(100),
    DepartmentID int, -- 这里存储员工所属的部门ID
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

注意

这里创建表的时候，要特别注意一个问题, 在创建 Employees 表时，它引用了尚未创建的 Departments 表的外键。
SQL Server 要求在创建外键约束时，被引用的表（即父表）必须已经存在 所以我们需要先创建 Departments 表，然后再创建 Employees 表, 但是我们在Employees 表中也有对Departments 表的引用，所以这样也不行!

解决方案

这里的解决办法就是先创建两个表，但不包括任何外键约束, 然后插入必要的测试数据,

最后再添加外键约束并且使用 ALTER TABLE 语句来添加就可以了!

因为我们使用的是SQL Server 2000 版本比较老旧, 所以我们的创建方案如下:

1.先创建 Employees 表，但不包括 DepartmentID 字段的外键约束
2.然后创建 Departments 表，但不包括 ManagerID 字段的外键约束

使用 ALTER TABLE 语句为 Employees 表添加 DepartmentID 字段的外键约束
使用 ALTER TABLE 语句为 Departments 表添加 ManagerID 字段的外键约束

注意

现在 Employees 表可能已经有数据了， 我们需要确保这些数据中的 EmployeeID 值与我们想设置为经理的 ManagerID 值相匹配

那么修改之后，我们的创建代码如下:

-- 创建 Employees 表，不包含外键约束
CREATE TABLE Employees (
    EmployeeID int PRIMARY KEY,
    EmployeeName nvarchar(100)
);
 
-- 创建 Departments 表，不包含外键约束
CREATE TABLE Departments (
    DepartmentID int PRIMARY KEY,
    DepartmentName nvarchar(100),
    ManagerID int -- 经理的员工ID，暂时不包含外键约束
);

如图

然后我们适当的插入一些数据~~到 Employees 表中

-- （可选）插入一些基础数据到 Employees 表中
INSERT INTO Employees (EmployeeID, EmployeeName) VALUES (1, '张三'); 
INSERT INTO Employees (EmployeeID, EmployeeName) VALUES (2, '李四'); 
INSERT INTO Employees (EmployeeID, EmployeeName) VALUES (3, '王五'); 
INSERT INTO Employees (EmployeeID, EmployeeName) VALUES (4, '雷恩'); 
INSERT INTO Employees (EmployeeID, EmployeeName) VALUES (5, '小吴'); 


-- （可选）插入一些基础数据到 Departments 表中，并手动设置 ManagerID
-- 假设张三是人力资源部的经理
INSERT INTO Departments (DepartmentID, DepartmentName, ManagerID) VALUES (1, '人力资源部', 1); 

如图

然后先向 Employees 表添加 DepartmentID 字段，然后再添加外键约束

如下

ALTER TABLE Employees ADD DepartmentID int;

然后为 Employees 表添加外键约束

如下

ALTER TABLE Employees ADD CONSTRAINT FK_Employee_Department FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);

如图

然后为 Departments 表添加外键约束

如下

ALTER TABLE Departments ADD CONSTRAINT FK_Department_Manager FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID);

如图

现在我们来看看这两个表的关系图 看看是不是相互引用的情况!

如图

此时，我们就建立了一种相互引用的关系!

现在我们再次添加两个部门

INSERT INTO Departments (DepartmentID, DepartmentName, ManagerID) VALUES (2, '技术部', 2); 
INSERT INTO Departments (DepartmentID, DepartmentName, ManagerID) VALUES (3, '推广部', 3); 

然后手动修改一下Employees表中的员工所属部门

--设置张三为 人力资源部员工
UPDATE Employees SET DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName='人力资源部') WHERE EmployeeID = 1
--设置李四为 技术部员工
UPDATE Employees SET DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName='技术部') WHERE EmployeeID = 2;
--设置王五为 推广部员工
UPDATE Employees SET DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName='推广部') WHERE EmployeeID = 3;
--设置雷恩为 技术部部员工
UPDATE Employees SET DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName='技术部') WHERE EmployeeID = 4;
--设置小吴为 推广部部员工
UPDATE Employees SET DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName='推广部') WHERE EmployeeID = 5;

如图

如图

现在，我们的数据库中有了以下数据：

Departments表包含3个部门，每个部门都有一个经理!

Employees表包含5个员工，每个员工都归属于某一个部门，并且前三个员工是各部门的经理!

那么此时你去删除这两个表中的彼此关联数据就会报错了!

如图

那现在万一我就是要删除技术部呢？并且下面的员工都被删除!

解决方案 创建临时数据

比如现在我们在这两个表Departments 表和 Employees表 都创建一条临时的记录!~

但是因为之前我们创建这两个表的时候，都创建了FOREIGN KEY约束，那么现在插入我们想要的临时数据肯定是不行的, 我们所以我们要重新创建这两个表~ 在创建外键之前，就把这两个临时数据添加进去!

如下

CREATE TABLE Employees (
    EmployeeID int PRIMARY KEY,
    EmployeeName nvarchar(100)
);
 
-- 创建 Departments 表，不包含外键约束
CREATE TABLE Departments (
    DepartmentID int PRIMARY KEY,
    DepartmentName nvarchar(100),
    ManagerID int -- 经理的员工ID，暂时不包含外键约束
);

--插入员工数据
INSERT INTO Employees (EmployeeID, EmployeeName) VALUES (1, '张三'); 
INSERT INTO Employees (EmployeeID, EmployeeName) VALUES (2, '李四'); 
INSERT INTO Employees (EmployeeID, EmployeeName) VALUES (3, '王五'); 
INSERT INTO Employees (EmployeeID, EmployeeName) VALUES (4, '雷恩'); 
INSERT INTO Employees (EmployeeID, EmployeeName) VALUES (5, '小吴'); 

--插入部门数据
INSERT INTO Departments (DepartmentID, DepartmentName, ManagerID) VALUES (1, '人力资源部', 1); 
INSERT INTO Departments (DepartmentID, DepartmentName, ManagerID) VALUES (2, '技术部', 2); 
INSERT INTO Departments (DepartmentID, DepartmentName, ManagerID) VALUES (3, '推广部', 3); 

--有了部门之后，我们在员工表中添加一个部门字段进行关联
ALTER TABLE Employees ADD DepartmentID int;

--添加临时数据
insert into Departments(DepartmentID,DepartmentName,ManagerID) VALUES (0,'temp临时部门',0);
insert into Employees(EmployeeID,EmployeeName,DepartmentID) VALUES (0,'temp临时员工',0);

--添加两个表彼此的关联关系

--Employees表 关联 Departments表
ALTER TABLE Employees ADD CONSTRAINT FK_Employee_Department FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);

--Departments表 关联 Employees表
ALTER TABLE Departments ADD CONSTRAINT FK_Department_Manager FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID);

如图

同时我们手动更新一下，员工的所属部门

UPDATE Employees SET DepartmentID  = 1 WHERE EmployeeID = 1;
UPDATE Employees SET DepartmentID  = 2 WHERE EmployeeID = 2;
UPDATE Employees SET DepartmentID  = 3 WHERE EmployeeID = 3;
UPDATE Employees SET DepartmentID  = 2 WHERE EmployeeID = 4;
UPDATE Employees SET DepartmentID  = 1 WHERE EmployeeID = 5;

如图

那么这一次，我们在这两个表里面就存有了临时数据了，然后我们执行一下查询看看创建的数据!

select * from Departments;
select * from Employees;

如图

修改指向到临时数据

举个栗子

那么我们现在要删除人力资源部，那就要先把它下面的员工都删除!

但是下面的有些员工，也引用了部门表, 进行了双向关联!

思路

现在我们来修改一下员工, 把它们的引用指向到一个临时数据上!~ 从而取消员工与部门的关联性!

然后再删除员工!

如下

update Employees set DepartmentID = 0 where DepartmentID = 1;

如图

相当于现在把这两个员工移入到了临时部门

那如果我们现在来删除部门，你看看可以了吗？

delete from Departments where DepartmentID = 1;

如图

虽然部门被删除了 但是员工还在呀~~

你也不可能来直接删除这个临时员工的信息，因为此时这些员工又属于临时部门

delete from Employees where DepartmentID = 0

这里我把这些员工的所属部门设置为NULL

如下

update Employees set DepartmentID = null where DepartmentID = 0 and EmployeeName != 'temp临时员工';

最后再删除这些员工~~

如下

delete from Employees where DepartmentID is NULL

如图

最后效果