如何防止自引用表成为循环表

本文关键字：循环 自引用 何防止 | 更新日期: 2023-09-27 17:58:07

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

我有一个表，它有一个指向自己PK的FK，以启用任意深度的层次结构，就像经典的tblEmployee一样，它的列Manager是一个带有PK tblEmpEmployee.EmployeeID.的FK

假设在我的应用程序中，用户

  1. 创建新员工Alice和Dave，没有经理，因为他们是首席执行官和总裁。因此，对于这两条记录，tblEmployee.Manager为NULL
  2. 创建新员工Bob，由Alice担任经理。然后创建Charles，Bob作为他的经理。它们的Manager字段包含tblEmployee中另一条记录的Primary Key值
  3. 为Alice编辑员工记录，这意味着分配Dave有她的经理（这很好），但意外地将Alice的经理设置为Charles，他在树上比Alice低两级

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

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

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

如何防止自引用表成为循环表

您可以使用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发生任何更改时都会被触发。

您可以添加'level'整数列。

Alice和Dave的等级为==0如果你将为员工设置经理，他的（员工）级别将是其经理的+1级。

在更新过程中，您应该检查经理级别是否低于员工级别。。。

这将比使用程序更快。。。

您可以在UPDATE语句中包含一个检查：

DECLARE @Employee INT = 2
       ,@NewManager INT = 4
;WITH cte AS (SELECT *
              FROM tblEmployee
              WHERE Manager = @Employee
              UNION  ALL
              SELECT a.*
              FROM tblEmployee a
              JOIN cte b
                ON a.manager = b.EmployeeID)
UPDATE a
SET a.Manager = @NewManager
FROM tblEmployee a
WHERE EmployeeID = @Employee
    AND NOT EXISTS (SELECT *
                    FROM cte b
                    WHERE a.EmployeeID = b.Manager)

演示：SQL Fiddle

我认为最好的方法是：

  1. 在t-sql中创建2个递归函数（执行比脏循环更好的循环），它将同时返回"N+x给定员工的经理"answers"N-x给定经理的员工

  2. 防止步骤3，使用GET_MANAGERS_OF和GET_EMPLOYEES_OF功能将在两者中使用：

    • 登录您的C#应用程序
    • 检查表EmployeeTRIGGER（最好的安全原因是你不知道是否每个开发人员都会在更新Employee之前进行检查，以及是否有人直接进行sql更新）

如果您分配给员工Y的经理X不是Y的员工N-X。

在任何情况下，这些递归函数都将在您的SQL查询和C#应用程序中使用

仅供参考，有一种方法可以在C#应用程序中处理SQL ERROR TRANSACTION（"你可以这么做是因为…"）。

相关文章：