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.