如何防止自引用表变成圆形 [英] How to prevent a self-referencing table from becoming circular

问题描述

这是一个非常普遍的问题，但是我还没有找到我想要的确切问题和答案.

This is a pretty common problem but I haven't yet found the exact question and answer I'm looking for.

我有一个表，其中的FK指向其自己的PK，以启用任意深度的层次结构，例如经典的tblEmployee，其列 Manager 是带有PK tblEmployee.EmployeeID的FK

I have one table that has a FK pointing to its own PK, to enable an arbitrarily deep hierarchy, like the classic tblEmployee that has a column Manager that is a FK with the PK tblEmployee.EmployeeID.

比方说，在我的应用中，用户

Let's say in my app, the user

1. 创建新员工Alice和Dave，没有经理，因为他们是CEO和总裁.因此，这两个记录的 tblEmployee.Manager 为NULL.
2. 创建新员工Bob，并以Alice为经理.然后以鲍勃为经理创建查尔斯.他们的经理"字段包含 tblEmployee 中另一条记录的主键值.
3. 编辑Alice的员工记录，这意味着指派Dave拥有她的经理(这很好)，但无意中将Alice的经理设置为Charles，比树中的Alice低两层.

1. Creates new employees Alice and Dave, with no manager because they're the CEO and President. So tblEmployee.Manager is NULL for those two records.
2. Create new employee Bob, with Alice as manager. Then create Charles with Bob as his manager. Their Manager fields contain the Primary Key value of another record in tblEmployee.
3. Edit employee record for Alice, meaning to assign Dave has her manager (which would be fine) but accidentally set Alice's manager to be Charles, who is two levels down from Alice in the tree.

现在该表处于循环引用中，而不是适当的树中.

Now the table is in a circular reference instead of a proper tree.

确保在应用程序中无法完成步骤3的最佳方法是什么?我只需要确保它会拒绝执行上一次SQL更新，而显示一些错误消息即可.

What is the best way to make sure that Step 3 cannot be done in an application? I just need to make sure that it will refuse to do that last SQL update, and instead show some error message.

对于它是SQL Server中的数据库约束(已在2008或2012年工作)还是在C#应用程序的业务逻辑层中使用某种验证例程，我并不挑剔.

I'm not picky about whether it's a database constraint in SQL Server (has to work in 2008 or 2012) or with some kind of validation routine in the business logic layer of my C# app.

推荐答案

您可以使用 CHECK CONSTRAINT 来执行此操作，以验证管理器ID是否不是循环.检查约束中不能包含复杂的查询，但是如果先将其包装在函数中，则可以:

You can do this with a CHECK CONSTRAINT that validates manager id is not a cycle. You can't have complex queries in a check constraint, but if you wrap it in a function first you can:

create function CheckManagerCycle( @managerID int )
returns int
as
begin

    declare @cycleExists bit
    set @cycleExists = 0

    ;with cte as (
        select E.* from tblEmployee E where ID = @managerID
        union all
        select E.* from tblEmployee E join cte on cte.ManagerID = E.ID and E.ID <> @managerID
    )
    select @cycleExists = count(*) from cte E where E.ManagerID = @managerID

    return @cycleExists;

end

然后您可以使用如下约束:

Then you can use a constraint like this:

alter table tblEmployee
ADD CONSTRAINT chkManagerRecursive CHECK ( dbo.CheckManagerCycle(ManagerID) = 0 )

这将防止添加或更新记录以从任何来源创建循环.

This will prevent adding or updating records to create a cycle from any source.

重要说明:检查约束在其引用的列上得到验证.我最初将其编码为检查员工ID而不是经理ID的周期.但是，这不起作用，因为它仅在对ID列进行更改时触发.此版本之所以有效，是因为只要 ManagerID 更改，便会触发该版本.

An important note: check constraints are validated on the columns they reference. I originally coded this to check cycles on the Employee ID, rather than the Manager ID. However, that did not work because it only triggered on changes to the ID column. This version does work because it is triggered any time the ManagerID changes.

这篇关于如何防止自引用表变成圆形的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！