【你也能从零基础学会网站开发】SQL Server 如何配置在多重循环级联路径场景下删除数据记录, 解决方案在这里!

🕗 发布于 2024-11-11 00:48 SQL Server 多重循环级联删除后端开发免费教程 FOREIGN KEY 约束

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

什么事多重级联路径

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

比如

表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表中的一个员工!

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

这里就有一个问题:

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

删除该部门下的所有员工, 因为员工归属于部门)
更新或删除作为该部门经理的员工记录因为经理是员工之一，但他们的特殊角色需要被处理!

然而，如果我们直接删除一个部门，并且该部门的经理仍然存在于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

如图

最后效果

"👍点赞" "✍️评论" "收藏❤️"
大家的支持就是我坚持下去的动力!

如果以上内容有任何错误或者不准确的地方，🤗🤗🤗欢迎在下面 👇👇👇 留个言指出、或者你有更好的想法，
欢迎一起交流学习❤️❤️💛💛💚💚

更多 好玩好用好看的干货教程可以 点击下方关注❤️ 微信公众号❤️
说不定有意料之外的收获哦..🤗嘿嘿嘿、嘻嘻嘻🤗!
🌽🍓🍎🍍🍉🍇

原文地址：https://blog.csdn.net/windowsxp2018/article/details/143642549

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：书生大模型基础岛第二关玩转书生「多模态对话」与「AI搜索」产品
下一篇：「QT」几何数据类之 QRectF 浮点型矩形类

通过物流分拣系统来理解RabbitMQ的消息机制
想象一个物流分拣系统，包裹穿梭于不同的分拣中心和配送站，最后精准地送达客户手中。RabbitMQ的消息传递机制就像这样的物流系统！
阅读更多2024-11-13
vue3中的组件通信方式有哪些？
vue3中常用的组件间通信方式
阅读更多2024-11-13
IT运维的365天--019 用php做一个简单的文件上传工具
前情提要：朋友的工作室，有几个网站分布在不同的服务器上，要经常进行更新，之前是手动复制压缩包到各个服务器去更新（有写了自动更新的Shell脚本）。于是有了这一篇文章。先是在域名的控制台新增一个A记录到
阅读更多2024-11-13
Python学习从0到1 day28 Python 高阶技巧 ⑤ 多线程
现代操作系统比如Mac OS X，UNIX，Linux，Windows等，都是支持“多任务”的操作系统。除了进程外，线程其实也是可以并行执行的，也就是比如一个Python程序,其实是完全可以做到。线程
阅读更多2024-11-13
01.Linux网络设置、FTP
第一张Linux网络设置、FTP
阅读更多2024-11-13
Wxml2Canvas小程序将dom转为图片，bug总结
我在做项目的时候遇到一个需求是将一个动态dom保存成图片这个功能，一开始想着要用canvas一点一点的去画，但是考虑到是动态的，这就导致会很麻烦，所以在网上到处查找资料，先是找到了html2Canva
阅读更多2024-11-13
信息收集、漏洞扫描、漏洞利用、权限提升、数据泄露
权限提升：在获得对目标系统的初步访问权限后，攻击者通常会尝试提升权限，以获得更高的系统控制权。数据泄露：在获得对目标系统的访问权限后，攻击者可能会窃取敏感数据，如用户信息、财务数据、商业机密等。成功的
阅读更多2024-11-13
【数据结构】【线性表】单链表1—概念即创建(附C语言源码)
由于有头结点的单链表具有天然的代码优势，因此在后续的内容中，如没有特殊说明，则代表该单链表为有头结点的单链表。两种，就链表定义而言，不需要头结点也是一个完整的链表，但在实际的运用中发现，没有头结点的单
阅读更多2024-11-13
30IP地址配置
通过今天的课程，你学习了如何在Linux系统中配置IP地址，包括静态配置和动态配置。掌握了这些基本技能后，你可以更好地管理你的Linux系统的网络设置，并确保其能够在网络环境中正常通信。如果你有任何问
阅读更多2024-11-13
kafka消费数据太慢了，给优化下
异步提交偏移量：你当前使用的是 consumer.commitSync() 进行同步提交偏移量，这会阻塞当前线程直到提交完成。合理设置 auto.commit.interval.ms：自动提交偏移量的
阅读更多2024-11-13

【你也能从零基础学会网站开发】SQL Server 如何配置在多重循环级联路径场景下删除数据记录, 解决方案在这里!

什么事多重级联路径

应用场景

解决方案 创建临时数据

修改指向到临时数据

相关文章

解决方案创建临时数据