如何通过存储过程从层级结构中检查标签重复？

现有表结构及测试数据 #

CREATE TABLE Label (
    IdLabel INT,
    IdParentLabel INT,
    Name VARCHAR(30)
);

INSERT INTO Label VALUES (1, NULL, 'root');
INSERT INTO Label VALUES (2, 1, 'child1');
INSERT INTO Label VALUES (3, 1, 'child2');
INSERT INTO Label VALUES (4, 2, 'grandchild1');
INSERT INTO Label VALUES (5, 3, 'grandchild2');

约束规则 #

新增标签时，同一分支的直系祖先（父、祖父直至根节点）中不能存在同名标签。例如：grandchild1（父节点为child1）的子标签不能命名为child1，但grandchild2（父节点为child2）的子标签可以命名为child1。

原有存储过程（不符合规则要求） #

原有存储过程错误地向下递归查询后代节点，而非向上检查祖先链，因此无法满足约束规则：

CREATE OR ALTER PROCEDURE [dbo].[CheckLabelExistsInHierarchy]
    @LabelName NVARCHAR(50),
    @IdParentLabel INT
AS
BEGIN
    SET NOCOUNT ON;
    
    WITH HierarchyCTE AS 
    (
        SELECT 
            IdLabel, IdParentLabel, Name
        FROM 
            Label
        WHERE 
            IdParentLabel = @IdParentLabel
    
        UNION ALL
    
        SELECT 
            l.IdLabel, l.IdParentLabel, l.Name
        FROM 
            Label l
        INNER JOIN 
            HierarchyCTE h ON l.IdLabel = h.IdParentLabel
    )
    SELECT 
        CAST(CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS BIT) AS Result
    FROM 
        HierarchyCTE
    WHERE 
        Name = @LabelName
END 

修正后的存储过程 #

通过向上递归遍历当前父节点的所有直系祖先，检查目标标签名是否已存在于祖先链中，符合约束规则：

CREATE OR ALTER PROCEDURE [dbo].[CheckLabelExistsInHierarchy]
    @LabelName NVARCHAR(50),
    @IdParentLabel INT
AS
BEGIN
    SET NOCOUNT ON;
    
    WITH AncestorCTE AS 
    (
        -- 起始节点：当前父节点本身
        SELECT 
            IdLabel, IdParentLabel, Name
        FROM 
            Label
        WHERE 
            IdLabel = @IdParentLabel
        
        UNION ALL
        
        -- 向上递归查找所有祖先节点
        SELECT 
            l.IdLabel, l.IdParentLabel, l.Name
        FROM 
            Label l
        INNER JOIN 
            AncestorCTE a ON l.IdLabel = a.IdParentLabel
    )
    -- 检查祖先链中是否存在同名标签
    SELECT 
        CAST(CASE WHEN EXISTS(SELECT 1 FROM AncestorCTE WHERE Name = @LabelName) THEN 1 ELSE 0 END AS BIT) AS Result;
END 

验证示例 #

• 调用EXEC CheckLabelExistsInHierarchy 'child1', 4（检查在grandchild1下新增child1）：返回1，不允许新增。
• 调用EXEC CheckLabelExistsInHierarchy 'child1', 5（检查在grandchild2下新增child1）：返回0，允许新增。

内容的提问来源于stack exchange，提问作者BlackCat