sql-server - 如何防止在 SQL 中插入循环引用

Question

我有下表:

create table dbo.Link
(
    FromNodeId int not null,
    ToNodeId int not null
)

此表中的行表示节点之间的链接。

我想防止对这个表的插入或更新在节点之间创建循环关系。

因此，如果该表包含:

(1,2)
(2,3)

不得包含以下任何内容:

(1,1)
(2,1)
(3,1)

如果它使解决方案更简单，我很高兴单独处理 (1,1) (例如使用 CHECK CONSTRAINT)。

我正在考虑使用递归 CTE 创建一个 AFTER INSERT 触发器(尽管可能有更简单的方法)。

假设这是要走的路，触发器定义是什么？如果有更优雅的方式，那是什么？

Answer 1

首先请注意，最好在另一个环境中检测循环，因为递归 CTE 并不以其良好的性能而著称，也不是为每个插入语句运行的触发器。对于大图，基于以下解决方案的解决方案可能效率低下。

假设您按如下方式创建表:

CREATE TABLE dbo.lnk (
    node_from INT NOT NULL,
    node_to INT NOT NULL,
    CONSTRAINT CHK_self_link CHECK (node_from<>node_to),
    CONSTRAINT PK_lnk_node_from_node_to PRIMARY KEY(node_from,node_to)
);

这将阻止插入 node_from等于 node_to , 以及已经存在的行。

如果检测到循环引用，以下触发器应通过抛出异常来检测循环引用:

CREATE TRIGGER TRG_no_circulars_on_lnk ON dbo.lnk AFTER INSERT
AS
BEGIN
    DECLARE @cd INT;
    WITH det_path AS (
        SELECT
            anchor=i.node_from,
            node_to=l.node_to,
            is_cycle=CASE WHEN i.node_from/*anchor*/=l.node_to THEN 1 ELSE 0 END
        FROM
            inserted AS i
            INNER JOIN dbo.lnk AS l ON
                l.node_from=i.node_to
        UNION ALL
        SELECT
            dp.anchor,
            node_to=l.node_to,
            is_cycle=CASE WHEN dp.anchor=l.node_to THEN 1 ELSE 0 END
        FROM
            det_path AS dp
            INNER JOIN dbo.lnk AS l ON
                l.node_from=dp.node_to
        WHERE
            dp.is_cycle=0
    )
    SELECT TOP 1
        @cd=is_cycle 
    FROM 
        det_path
    WHERE
        is_cycle=1
    OPTION 
        (MAXRECURSION 0);

    IF @cd IS NOT NULL 
        THROW 67890, 'Insert would cause cyclic reference', 1;
END

我对有限数量的插入物进行了测试。

INSERT INTO dbo.lnk(node_from,node_to)VALUES(1,2); -- OK
INSERT INTO dbo.lnk(node_from,node_to)VALUES(2,3); -- OK
INSERT INTO dbo.lnk(node_from,node_to)VALUES(3,4); -- OK

和

INSERT INTO dbo.lnk(node_from,node_to)VALUES(2,3); -- PK violation
INSERT INTO dbo.lnk(node_from,node_to)VALUES(1,1); -- Check constraint violation
INSERT INTO dbo.lnk(node_from,node_to)VALUES(3,2); -- Exception: Insert would cause cyclic reference
INSERT INTO dbo.lnk(node_from,node_to)VALUES(3,1); -- Exception: Insert would cause cyclic reference
INSERT INTO dbo.lnk(node_from,node_to)VALUES(4,1); -- Exception: Insert would cause cyclic reference

如果一次插入多于一行，或者如果在图中引入比一条边长的路径，它还会检测插入行中已经存在的循环引用。开始相同的初始插入:

INSERT INTO dbo.lnk(node_from,node_to)VALUES(8,9),(9,8);       -- Exception: Insert would cause cyclic reference
INSERT INTO dbo.lnk(node_from,node_to)VALUES(4,5),(5,6),(6,1); -- Exception: Insert would cause cyclic reference