Prevent Circular Hierarchy in SQL Server (Employee Becomes Their Own Manager)

A circular hierarchy happens when a reporting structure forms a loop, so an employee ends up reporting to themselves—directly or indirectly.

For example,

AàA

AàBàCàA

“SQL Server does not prevent this automatically — we must handle it ourselves.”

Let’s see the demo

First, we will replicate this issue and then we will see the solution for the same.

CREATE TABLE Emptbl (     EmpID INT PRIMARY KEY,     EmpName VARCHAR(50),     ManagerID INT NULL,                  CONSTRAINT FK_Emp_Mgr         FOREIGN KEY (ManagerID)         REFERENCES Emptbl(EmpID) ); --inserting few records insert Emptbl (EmpID,EmpName,ManagerID) values (1,'Bagesh',null) insert Emptbl (EmpID,EmpName,ManagerID) values (2,'Ramesh',1) insert Emptbl (EmpID,EmpName,ManagerID) values (3,'Suresh',1) insert Emptbl (EmpID,EmpName,ManagerID) values (4,'Mahesh',2) insert Emptbl (EmpID,EmpName,ManagerID) values (5,'Umesh',2) insert Emptbl (EmpID,EmpName,ManagerID) values (6,'Ganesh',4)

 See the data

Below Script is used to get the subordinates details.

WITH OrgCTE AS (     -- Anchor (manager)     SELECT         EmpID,EmpName,ManagerID,                            0 AS Level,         CAST(EmpName AS VARCHAR(MAX)) AS HierarchyPath     FROM Emptbl     --WHERE ManagerID is null   -- Manager A                  UNION ALL     -- Recursive     SELECT e.EmpID,e.EmpName, e.ManagerID,                                 c.Level + 1,         CAST(c.HierarchyPath + ' > ' + e.EmpName AS VARCHAR(MAX))     FROM Emptbl e     JOIN OrgCTE c         ON e.ManagerID = c.EmpID ) SELECT * FROM OrgCTE ORDER BY Level;

 

Now one employee insert one records as below

insert Emptbl (EmpID,EmpName,ManagerID) values (7,'Ramu',7)

This record has same empID and ManagerID.

Now we are running the above query

And getting error.

Because it Circular Hierarchy and it falls in infinite loop.

To prevent this, we need to restrict the wrong Entry in this table.

Ø  We can create a check constrain to avoid the wrong entry

Ø  Create a trigger to prevent the wrong entry in the table

Check constraint

Adding check constraint

ALTER TABLE Emptbl ADD CONSTRAINT CK_NoSelfManager CHECK (EmpID <> ManagerID);

 Running this script

Ooohhh!!! Its got failed.

Before creating this constrain we need to clean the data. Here we are deleting the bad record.

delete from Emptbl where empId=7

Now creating the constraint

Let’s insert the bad record.

In this way we can prevent the wrongs data entry.

Another way to prevent that we need to create a trigger on this table.

Before creating the trigger we are deleting the constraint

ALTER TABLE Emptbl drop CONSTRAINT CK_NoSelfManager

Now we are creating the Trigger on this table

CREATE TRIGGER trg_PreventCircularHierarchy ON Emptbl AFTER INSERT, UPDATE AS BEGIN     IF EXISTS (         SELECT 1         FROM inserted i         WHERE i.ManagerID = i.EmpID     )     BEGIN         THROW 50001, 'Employee cannot be their own manager', 1;     END END;

Trigger created

Now we are trying to insert the wrong record.

In this way we can prevent the wrong entry on this table and our query will not fail.