SQL Server 2008 – Create Constraint to Prevent Circularity in Hierarchical Tables

constrainthierarchysql-server-2008

Suppose we have a table that has a foreign key constraint to itself, like such:

CREATE TABLE Foo 
    (FooId BIGINT PRIMARY KEY,
     ParentFooId BIGINT,
     FOREIGN KEY([ParentFooId]) REFERENCES Foo ([FooId]) )

INSERT INTO Foo (FooId, ParentFooId) 
VALUES (1, NULL), (2, 1), (3, 2)

UPDATE Foo SET ParentFooId = 3 WHERE FooId = 1

This table will have the following records:

FooId  ParentFooId
-----  -----------
1      3
2      1
3      2

There are cases where this kind of design could make sense (e.g. the typical "employee-and-boss-employee" relationship), and in any case: I'm in a situation where I have this in my schema.

This kind of design unfortunately allows for circularity in data records, as shown in the example above.

My question then is:

  1. Is it possible to write a constraint that checks this? and
  2. Is it feasible to write a constraint that checks this? (if needed only to a certain depth)

For part (2) of this question it may be relevant to mention that I expect only hundreds or perhaps in some cases thousands of records in my table, normally not nested any deeper than about 5 to 10 levels.

PS. MS SQL Server 2008

Update March 14th 2012
There were several good answers. I've now accepted the one that helped me understand mentioned possibility/feasibility. There are several other great answers though, some with implementation suggestions as well, so if you landed here with the same question have a look at all answers 😉

Best Answer

You are using the Adjacency List model, where it is difficult to enforce such a constraint.

You can examine the Nested Set model, where only true hierarchies can be represented (no circular paths). This has other drawbacks though, like slow Inserts/Updates.

Related Question