使用MSSQL层次结构检查约束时，如何摆脱超时错误

Question

我正在创建SQL Hirercky Table

这是我的代码;

约束功能代码

                    alter Function Accounts.Types_Sub_Check_fn (@ID uniqueidentifier, @Sub Uniqueidentifier) returns int
                    begin
                    --declare @id uniqueidentifier = '8c7d4151-246c-476c-adf6-964ca9afdd3c' declare @sub uniqueidentifier = '47c2b6da-25fc-4921-adfa-b1f635bddde6'
                    declare @a int
                    declare @b int =(iif(@ID=@SUB,2,0))
                    ;with cte(id, lvl) as
                      (
                        select f.sub,
                               1
                        from Accounts.Types as f
                        where f.id = @id
                        union all
                        select f.sub,
                               lvl + 1
                        from Accounts.Types as f
                          inner join cte as c
                            on f.id = c.id
                      )
                      select @a = (select count (*)
                                     from cte
                                     where id =@sub) + @b

                      option (maxrecursion 0)

                        return @a
                    end
                    go

表代码

   create Table Accounts.Types
                        (
                        ID uniqueidentifier  not null CONSTRAINT DF_Accounts_Types_ID DEFAULT newid() CONSTRAINT PK_Accounts_Types_ID PRIMARY KEY NONCLUSTERED (ID) ,
                        Name varchar(200) not null CONSTRAINT UQ_Accounts_Types_NAME UNIQUE (NAME), 
                        Sub uniqueidentifier CONSTRAINT FK_Accounts_Types_Sub Foreign key references Accounts.Types ,
                        Ctype uniqueidentifier CONSTRAINT FK_Accounts_Types_Ctype Foreign key references Accounts.Types ,
                        insert_time datetime not null CONSTRAINT DF_Accounts_Types_Insert_Time DEFAULT getdate() ,
                        insert_user uniqueidentifier CONSTRAINT DF_Accounts_Types_Insert_User DEFAULT'9EC66F53-9233-4A6C-8933-F8417D2BB5A9'  ,
                        ts timestamp,
                        INDEX IX_Accounts_Types_NAME#ASC CLUSTERED (Name ASC),
                        Constraint Check_Accounts_Types_Sub check (Accounts.Types_Sub_Check_fn(ID,Sub)<=1)
                        )
                        go

如果试图将ITSeft插入父（在子列中）

，则此功能将给出2个结果。

如果已经是一个孩子，它将给出1个试图插入其父

的孩子

创建检查约束是为了检查任何ID的父（子列）是否不应是其孩子或大孩子，而且本身不能是其父母

当我尝试插入与检查约束不匹配的数据时，它会卡住并给出超时错误，

eg：

   insert into Accounts.Types (ID, Name, Sub)
   values ('607936b9-6f95-4989-8ebe-87a08807f43e','LLL','607936b9-6f95-4989-8ebe-87a08807f43e')

这将提供超时

任何人都可以帮我，我需要摆脱超时错误；仅获取约束错误

Answer 1

简单问题 - 当您的ID和SUB是相同的值而您不限制MaxRecursion或LVL时，您的递归何时结束？绝不。它永远不会结束。

values ('607936b9-6f95-4989-8ebe-87a08807f43e','LLL','607936b9-6f95-4989-8ebe-87a08807f43e')

您必须删除行iD = sub或添加maxRecursion或添加级别限制或标准化表的行。

Answer 2

            alter Function Accounts.Types_Sub_Check_fn (@ID uniqueidentifier, @Sub Uniqueidentifier) returns int
                    begin
                    --declare @id uniqueidentifier = '00279c6b-df00-4144-810d-571fdb1c5109' declare @sub uniqueidentifier = 'bc887e7b-36d2-4ece-8ec1-720dc81a9de4'
                    declare @a int = 0
                    declare @b int =(iif(@ID=@SUB,2,0))
                    if @ID <> @sub
                    begin
                    ;with cte(id, lvl) as
                      (
                        select f.Sub ,
                               1
                        from Accounts.Types as f
                        where f.id = @sub 
                        union all
                        select  iif(f.Sub = @sub, Null, f.sub),
                               lvl + 1
                        from Accounts.Types as f
                          inner join cte as c
                            on f.id = c.id 
                      )
                      select @a = (select count (*)
                                     from cte
                                     where id =@id) 
        option (maxrecursion 0);

                      end
                    --  select @a + @b

                        return @a + @b
                    end
                    go