多级联路径sql server 2017创建自引用外键
在SQL Server 2017中创建自引用外键，特别是在多级联路径的情况下，涉及到数据库设计中的递归关系。以下是关于这个问题的基础概念、优势、类型、应用场景以及可能遇到的问题和解决方案的详细解释。
基础概念
自引用外键是指一个表中的字段引用了该表中的另一个字段。这种设计通常用于表示层次结构，如组织结构、分类系统等。
优势
- 数据完整性：通过外键约束确保数据的引用完整性。
- 简化查询：可以直接在数据库层面处理层次关系，减少应用程序的复杂性。
- 性能优化：某些查询可以利用索引和递归CTE（Common Table Expressions）来提高效率。
类型
- 单级自引用：表中的一个字段引用同一表中的另一个字段。
- 多级自引用：表中的字段通过多个层级引用自身，形成复杂的层次结构。
应用场景
- 组织结构图：如公司内部的部门层级。
- 分类系统：如商品分类的多级目录。
- 文件系统模拟：文件夹和子文件夹的关系。
创建多级联路径自引用外键的步骤
假设我们有一个Employees表，每个员工可以有多个下属，形成一个多层次的管理结构。
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(100),
ManagerID INT,
FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)
);可能遇到的问题及解决方案
问题1：插入数据时违反外键约束
原因：尝试插入一个不存在的上级记录。 解决方案：确保在插入新记录之前，其上级记录已经存在。
-- 错误示例
INSERT INTO Employees (EmployeeID, Name, ManagerID) VALUES (3, 'Alice', 4); -- ManagerID 4 不存在
-- 正确示例
INSERT INTO Employees (EmployeeID, Name, ManagerID) VALUES (2, 'Bob', 1); -- 先插入上级记录
INSERT INTO Employees (EmployeeID, Name, ManagerID) VALUES (3, 'Alice', 2);问题2：查询多级层次结构时的性能问题
原因：递归查询可能导致性能下降，特别是在数据量大的情况下。 解决方案：使用索引优化查询，并考虑限制递归深度。
CREATE INDEX idx_ManagerID ON Employees(ManagerID);
WITH RecursiveEmployees AS (
SELECT EmployeeID, Name, ManagerID, 0 AS Level
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.Name, e.ManagerID, re.Level + 1
FROM Employees e
INNER JOIN RecursiveEmployees re ON e.ManagerID = re.EmployeeID
)
SELECT * FROM RecursiveEmployees;示例代码
以下是一个完整的示例，展示了如何在SQL Server 2017中创建和使用多级联路径自引用外键。
-- 创建表
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(100),
ManagerID INT,
FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)
);
-- 插入数据
INSERT INTO Employees (EmployeeID, Name, ManagerID) VALUES (1, 'CEO', NULL);
INSERT INTO Employees (EmployeeID, Name, ManagerID) VALUES (2, 'CTO', 1);
INSERT INTO Employees (EmployeeID, Name, ManagerID) VALUES (3, 'Alice', 2);
INSERT INTO Employees (EmployeeID, Name, ManagerID) VALUES (4, 'Bob', 2);
-- 查询多级层次结构
WITH RecursiveEmployees AS (
SELECT EmployeeID, Name, ManagerID, 0 AS Level
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.Name, e.ManagerID, re.Level + 1
FROM Employees e
INNER JOIN RecursiveEmployees re ON e.ManagerID = re.EmployeeID
)
SELECT * FROM RecursiveEmployees;通过这种方式，可以有效地管理和查询复杂的层次结构数据。
相关·内容
1回答
在Sql Server 2012中，我有一个employee表。如下所示并且该表在ManagerId和Id之间有一个外键。问题是我得到了一个外键异常(使用ef和级联删除)。如何删除具有自引用外键的表中的行。
浏览 2提问于2013-10-16得票数 1
FK_dbo.acct_Account_dbo.acct_Account_ParentId‘：System.Data.SqlClient.SqlException: System.Data.SqlClient.SqlException:在表'acct_Account’上引入外键约束acct_Account可能会导致循环或多个级联路径。指定“删除不操作”或“更新不操作”，或修改其他外键约束。无法创建约束。
我读过EF的文件，但我不明白我的代码哪里有问题.如
浏览 1提问于2014-07-31得票数 5
回答已采纳
我有自引用的目录对象.每个目录都有子目录的父目录和列表。FK_DirectoryReference_DirectoryReference_ParentId‘ Microsoft.Data.SqlClient.SqlException：“在表'DirectoryReference’上引入外键约束DirectoryReference可能会导致循环或多个级联路径。指定“删除不操作”或“更新不操作”，或修改其他外键约束。无法创建约束
浏览 3提问于2021-06-21得票数 2
3回答
MS中的自引用约束
、、、
MS是否确实使用DELETE级联选项来限制自引用约束？我有一个具有父-子关系的表，PARENT_ID列是ID的外键。
我不敢相信我必须在递归模式下删除这个层次结构。除了触发器，还有什么问题吗？
浏览 3提问于2009-02-09得票数 11
回答已采纳
1回答
e.ToTable("DocumentMappings"); }免责声明:我看到一些帖子说这是不可能的，因为你“不能在SQL SERVER中的自引用表上使用级联删除。”()。
浏览 1提问于2015-09-19得票数 0
.HasForeignKey(x => x.ReferringStudentID);
问题：
如何才能让实体框架认识到ReferringStudentID应该是一个外键
浏览 3提问于2011-10-14得票数 0
回答已采纳
2回答
我试图在Server中的同一个表之间创建一个多对多的关系。
在表'FK_ObjectRelation_Object1‘上引入外键约束'tblADMembership’可能会导致循环或多个<e
浏览 3提问于2012-11-02得票数 7
回答已采纳
1回答
多到多层叠删除风
、、、、
C和D之间存在着多到多的关系。我希望建立一个级联删除，以便删除以下任何一行中的第一个实体，从而产生级联删除。A -> B -> D -> CD
所有其他外键都有级联删除( -> C除外)。每次尝试将其设置为级联删除
浏览 2提问于2014-10-31得票数 0
我有一个与父表(TableA)具有外键关系的表(TableB)。我也不能使用INSTEAD OF触发器，因为TableA本身具有级联外键关系。我想要做的是将TableA和TableB之
浏览 1提问于2012-01-28得票数 4
回答已采纳
1回答
我有一个自我引用的评论表。我试着在delete级联上写，但是它有一些例外。
CREATE TRIGGER [dbo].AS WHERE ParentId =(SELECT de
浏览 2提问于2017-02-14得票数 6
回答已采纳
1回答
n.MapLeftKey("From").MapRightKey("To").ToTable("NodeEdge")););
是否有任何方法在联接表约束上添加ON DELETE CASCADE，以便在我删除节点时清除所有引用？(.和保持模型简单，而没有连接表实体)
浏览 3提问于2015-11-21得票数 1
回答已采纳
1回答
ON DELETE RESTRICTENGINE = InnoDB我正在尝试将它转换为ServerON DELETE RESTRICT：我不确定与此等价的SQL。据我所知，它可能是NO ACTION，但我不确定。我正在使用SQL (MS 2014)，在将所有ON DELETE RESTRICT替换为ON DELETE NO ACTION之后，它显示了以下错误：
在表‘专业’上引入外键约束'
浏览 0提问于2015-11-17得票数 0
父表具有列town，该列包含带有Town (townid, townname)的外键引用，并且该城镇添加了外键引用。现在的问题是，我需要为人工表提供级联约束，但是在Server中，同一个父表的多列不能给出级联。但是，当我试图执行触发器时，系统会显示以下错误
不能在表父级上创建而不是删除或更新触发器DEL_Parent。这是因为表中有一个外键，其中包含级联
浏览 2提问于2013-02-28得票数 3
回答已采纳
2回答
我一直在寻找这个问题的答案，但是在我正在工作的数据库模型中的两个特定表之间实现级联删除条件时遇到了困难。当我开始部署时，Server一直抛出一个错误，说明可能存在循环或多个级联路径。fooid|Column1|Column2...1接下来，考虑一个表条，它有两个列，每个列都包含对foo中id的外键引用。当我试图在两个外键行中实现带有"ON“的级联delete时，
浏览 0提问于2018-11-21得票数 0
回答已采纳
1回答
两个参考文献中的EF 6级联
、、、、
虽然此类问题有多种解决方案，但它们不允许多个级联路径，而只是从一个引用中禁用delete级联选项。 [ForeignKey("CourseId")] }
我还试图通过在两个外键中添加DELETE级联更新级联来手动修改数据库，但是SQ
浏览 0提问于2014-10-27得票数 0
3回答
自引用外键约束和删除
、、、、
在Server中处理自引用外键约束的推荐方法是什么？
fiData引用tabData中的前一条记录。如果删除fiData引用的记录，数据库将引发异常：
“DELETE语句与相同的表引用约束"FK_tabDataPrev_tabDataNext”冲突。我不需要级联删除被引用的记录，但是我需要在引用的地方设置fiData=NULL。我的想法是将Enforce Foreig
浏览 2提问于2011-03-24得票数 7
回答已采纳
Server 2016系统版本控制是很酷的。我正在使用免费的开发者版本。谢谢小姐！
就像。SQL Server 2016是否支持此操作？如果没有，还有其他方法可以做到吗？
浏览 0提问于2016-06-09得票数 11
回答已采纳
1回答
我目前正在使用.sdf数据库(ServerCompactVersion4.0)和sql。我试图在同一个表(类别-子类别)上设置一个级联删除，但我知道我不能将关系添加到同一个表中。不允许使用外键约束具有并更新或删除级联规则，并且不允许自引用同一表中的列。
编辑
只有我有这个问题？
浏览 2提问于2011-02-08得票数 6
回答已采纳
扫码
添加站长 进交流群
领取专属 10元无门槛券
手把手带您无忧上云
相关资讯
热门标签
云服务器
ICP备案
对象存储
腾讯会议
云直播活动推荐
腾讯云开发者
