Prevent circular reference in MS-SQL table

Question

I have a Account table with ID and ParentAccountID. Here is the scripts to reproduce the steps.

If the ParentAccountID is NULL then that is considered as Top level account. Every account should finally ends with top level account i.e ParentAccountID is NULL

    Declare @Accounts table (ID INT, ParentAccountID INT )    


    INSERT INTO @Accounts values (1,NULL), (2,1), (3,2) ,(4,3), (5,4), (6,5)

    select * from @Accounts

     -- Request to update ParentAccountID to 6 for the ID 3
    update @Accounts  
    set ParentAccountID = 6
    where ID = 3

    -- Now the above update will cause circular reference 
    select * from @Accounts

When request comes like to update ParentAccountID of an account, if that cause circular reference then before update its need to identified.

Any idea folks!!

Answer 1

It seems you've got some business rules defined for your table:

• All chain must end with a top-level account
• A chain may not have a circular reference

You have two ways to enforce this.

You can create a trigger in your database, and check the logic in the trigger. This has the benefit of running inside the database, so it applies to every transaction, regardless of the client. However, database triggers are not always popular. I see them as a side effect, and they can be hard to debug. Triggers run as part of your SQL, so if they are slow, your SQL will be slow.

The alternative is to enforce this logic in the application layer - whatever is talking to your database. This is easier to debug, and makes your business logic explicit to new developers - but it doesn't run inside the database, so you could end up replicating the logic if you have multiple client applications.

Answer 2

Here is an example that you could use as a basis to implement a database constraint that should prevent circular references in singular row updates; I don't believe this will work to prevent a circular reference if multiple rows are updated.

/*
ALTER TABLE dbo.Test  DROP CONSTRAINT chkTest_PreventCircularRef
GO
DROP FUNCTION dbo.Test_PreventCircularRef 
GO
DROP TABLE dbo.Test 
GO
*/

CREATE TABLE dbo.Test (TestID INT PRIMARY KEY,TestID_Parent INT)
INSERT INTO dbo.Test(TestID,TestID_Parent) SELECT 1 AS TestID,NULL  AS TestID_Parent
INSERT INTO dbo.Test(TestID,TestID_Parent) SELECT 2 AS TestID,1     AS TestID_Parent
INSERT INTO dbo.Test(TestID,TestID_Parent) SELECT 3 AS TestID,2     AS TestID_Parent
INSERT INTO dbo.Test(TestID,TestID_Parent) SELECT 4 AS TestID,3     AS TestID_Parent
INSERT INTO dbo.Test(TestID,TestID_Parent) SELECT 5 AS TestID,4     AS TestID_Parent
GO

GO
CREATE FUNCTION dbo.Test_PreventCircularRef (@TestID INT,@TestID_Parent INT)
RETURNS INT
BEGIN
    --FOR TESTING:
    --SELECT * FROM dbo.Test;DECLARE @TestID INT=3,@TestID_Parent INT=4

    DECLARE @ParentID INT=@TestID
    DECLARE @ChildID INT=NULL
    DECLARE @RetVal INT=0
    DECLARE @Ancestors TABLE(TestID INT)
    DECLARE @Descendants TABLE(TestID INT)

    --Get all descendants
    INSERT INTO @Descendants(TestID) SELECT TestID FROM dbo.Test WHERE TestID_Parent=@TestID
    WHILE (@@ROWCOUNT>0)
    BEGIN
        INSERT INTO @Descendants(TestID)
            SELECT t1.TestID
            FROM dbo.Test t1
            LEFT JOIN @Descendants relID ON relID.TestID=t1.TestID
            WHERE relID.TestID IS NULL
            AND t1.TestID_Parent IN (SELECT TestID FROM @Descendants)
    END

    --Get all ancestors
    --INSERT INTO @Ancestors(TestID) SELECT TestID_Parent FROM dbo.Test WHERE TestID=@TestID
    --WHILE (@@ROWCOUNT>0)
    --BEGIN
    --  INSERT INTO @Ancestors(TestID)
    --      SELECT t1.TestID_Parent
    --      FROM dbo.Test t1
    --      LEFT JOIN @Ancestors relID ON relID.TestID=t1.TestID_Parent
    --      WHERE relID.TestID IS NULL
    --      AND t1.TestID_Parent IS NOT NULL
    --      AND t1.TestID IN (SELECT TestID FROM @Ancestors)
    --END

    --FOR TESTING:
    --SELECT TestID AS [Ancestors] FROM @Ancestors;SELECT TestID AS [Descendants] FROM @Descendants;

    IF EXISTS (
        SELECT *
        FROM @Descendants
        WHERE TestID=@TestID_Parent
    )
    BEGIN
        SET @RetVal=1
    END

    RETURN @RetVal
END
GO

ALTER TABLE dbo.Test 
  ADD CONSTRAINT chkTest_PreventCircularRef
  CHECK (dbo.Test_PreventCircularRef(TestID,TestID_Parent) = 0); 
GO

SELECT * FROM dbo.Test

--This is problematic as it creates a circular reference between TestID 3 and 4; it is now prevented
UPDATE dbo.Test SET TestID_Parent=4 WHERE TestID=3

Answer 3

Dealing with self-referencing tables / recursive relationships in SQL is not simple. I suppose this is evidenced by the fact that multiple people can't get their heads around the problem with just checking for single-depth cycles.

To enforce this with table constraints, you would need a check constraint based on a recursive query. At best that's DBMS-specific support, and it may not perform well if it has to be run on every update.

My advice is to have the code containing the UPDATE statement enforce this. That could take a couple of forms. In any case if it needs to be strictly enforced it may require limiting UPDATE access into the table to a service account used by a stored proc or external service.

Using a stored procedure would be vary similar to a CHECK constraint, except that you could use procedural (iterative) logic to look for cycles before doing the update. It has become unpopular to put too much logic in stored procs, though, and whether this type of check should be done is a judgement call from team to team / organization to organization.

Likewise using a service-based approach would let you use procedural logic to look for cycles, and you could write it in a language better suited to such logic. The issue here is, if services aren't part of your architecture then it's a bit heavy-weight to introduce a whole new layer. But, a service layer is probably considered more modern/popular (at the moment at least) than funneling updates through stored procs.

With those approaches in mind - and understanding that both procedural and recursive syntax in databases is DBMS-specific - there are too many possible syntax options to really go into. But the idea is:

• Examine the proposed parent.
• Check it's parent recursively
• Do you ever reach the proposed child before reaching a top-level account? IF not, allow the update

Answer 4

Finally, I have created the scripts after some failures, its working fine for me.

   -- To hold the Account table data
   Declare @Accounts table (ID INT, ParentAccountID INT) 

   -- To be updated 
   Declare @AccountID       int = 4;
   Declare @ParentAccountID int = 7;

   Declare @NextParentAccountID INT = @ParentAccountID

   Declare @IsCircular int = 0

   INSERT INTO @Accounts values (1, NULL), (2,1), (3,1) ,(4,3), (5,4), (6,5), (7,6), (8,7)

   -- No circular reference value
   --Select * from @Accounts

   -- Request to update ParentAccountID to 7 for the Account ID 4
   update @Accounts  
   set ParentAccountID = @ParentAccountID
   where ID = @AccountID

   Select * from @Accounts

   WHILE(1=1)
   BEGIN            
       -- Take the ParentAccountID for @NextParentAccountID
       SELECT @NextParentAccountID = ParentAccountID from @Accounts WHERE ID = @NextParentAccountID  

       -- If the @NextParentAccountID is NULL, then it reaches the top level account, no circular reference hence break the loop 
       IF (@NextParentAccountID IS NULL) 
       BEGIN
        BREAK;
       END

       -- If the @NextParentAccountID is equal to @AccountID (to which the update was done) then its creating circular reference
       -- Then set the @IsCircular to 1 and break the loop
       IF (@NextParentAccountID = @AccountID ) 
       BEGIN
        SET @IsCircular = 1
        BREAK
       END
   END

IF @IsCircular = 1 
    BEGIN 
        select 'CircularReference' as 'ResponseCode'
    END