How to get rid of timeout error when using mssql hierarchy check constrint

Question

i am creating sql hirercky table

Here is my code;

The Constraint Function Code

                    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

The Table code

   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

This function will give 2 as result if trying to insert itseft as parent (in sub column)

it will give 1 if its already a child, which trying to insert as its parent

The Check constraint is created to check if the the parent (sub column) for any id should not be its child or grand child, and itself cannot be its parent

When i try to insert a data which does not match the check constraint, it stuck, and give a timeout error,

eg:

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

this will give timeout

can anyone help me out, i need to get rid of time out error; get the constraint error only

It looks like you're checking for parent/child/self relationships by building a CSV string and then doing a LIKE on it. You probably could check for the relationships directly using the recursive CTE without building the CSV string. — Ann L., Dec 02 '16 at 14:44
Have you ruled out using the build in support for modelling hierarchies, via the `hierarchyid` datatype? — Damien_The_Unbeliever, Dec 02 '16 at 14:44
If you create a loop in the "tree", then the CTE will run for a while. [This](http://stackoverflow.com/a/15081353/92546) answer shows a way to terminate the recursion if a loop is detected. — HABO, Dec 02 '16 at 14:49

score 1 · Accepted Answer · answered Dec 02 '16 at 20:09

1

Easy question - when will your recursion end when your ID and Sub are the same values and you don't limit maxrecursion or lvl? Never. It'll never end.

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

You have to remove rows where ID = Sub or add maxrecursion or add level limit or normalize your table.

answered Dec 02 '16 at 20:09

Deadsheep39

561
3
16

you are right it never ends; becuz check constraint check after inserting it – Hussain Nasif Dec 02 '16 at 22:14
just replaced the line--------------------------------------------------------------------------------- union all select f.sub, ------------------------------------------------------------------to ------------------------------------------------------------------------------------------------------------union all select iif(f.Sub = @sub, Null, f.sub), ------------------------------------------------------------------ to make curent updated record's sub column to null to end the loop. – Hussain Nasif Dec 02 '16 at 23:06

Hussain Nasif · Answer 2 · 2016-12-02T23:02:11.930

            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

How to get rid of timeout error when using mssql hierarchy check constrint

2 Answers2