您可以使用 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.