如何防止自引用表变成圆形

Question

这是一个非常常见的问题，但我尚未找到我要寻找的确切问题和答案。

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

假设在我的应用中，用户

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

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

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

对于它是SQL Server中的html" target="_blank">数据库约束（必须在2008年或2012年工作）还是在C＃应用程序的业务逻辑层中使用某种验证例程，我并不挑剔。

Answer 1

您可以CHECK CONSTRAINT通过验证管理员ID不是周期来执行此操作。检查约束中不能包含复杂的查询，但是如果首先将其包装在函数中，则可以：

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

然后，您可以使用如下约束：

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

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

编辑：
一个重要的注意事项：检查约束在它们引用的列上得到验证。我最初将其编码为检查员工ID而不是经理ID的周期。但是，这不起作用，因为它仅在对ID列进行更改时触发。该版本之所以起作用，是因为它在ManagerID更改时会被触发。