CELKO (1/31/2011)
Nice piece! Now let's go one step further. How do you write constraints to prevent cycles (and orphans) in an adjacency list model?The best I have done for the adjacency list in a TRIGGER with a loop which is what your recursive CTE is, under the covers. But the loop does not have a built-in limit of 32.
Orphans? No problem--that's what foreign key constraints are there for.
Cycles? A bit trickier, but easy enough. First step, a check constraint so that an employee can't be his or her own manager. (We'll say that the manager for the root node is NULL.) Next, a unique constraint on the employee column, so that the same employee can't appear in the hierarchy twice under different managers. That eliminates most cycles, but we still have a potential issue where we can insert the following data:
Mgr Emp
NULL A
A B
B C
And then do:
UPDATE tbl
SET Mgr = 'C'
WHERE Emp = 'B'
... getting rid of that requires a trigger; below is one I came up with when working on my book, "Expert SQL Server 2005 Development." The idea is to start at the updated node(s) and walk up the hierarchy toward the root. If, during that walk, we find a case where the parent "manager" is the same as the child "employee," we know that something is amiss:
CREATE TRIGGER tg_NoCycles
ON Employee_Temp
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON
--Only check if the ManagerId column was updated
IF NOT UPDATE(ManagerId)
RETURN
--Avoid cycles
DECLARE @CycleExists BIT
SET @CycleExists = 0
--Traverse up the hierarchy toward the
--root node
;WITH e AS
(
SELECT EmployeeId, ManagerId
FROM inserted
UNION ALL
SELECT e.EmployeeId, et.ManagerId
FROM Employee_Temp et
JOIN e ON e.ManagerId = et.EmployeeId
WHERE
et.ManagerId IS NOT NULL
AND e.ManagerId <> e.EmployeeId
)
SELECT @CycleExists = 1
FROM e
WHERE e.ManagerId = e.EmployeeId
IF @CycleExists = 1
BEGIN
RAISERROR('The update introduced a cycle', 16, 1)
ROLLBACK
END
END
GO
--
Adam Machanic
whoisactive