Tables with hierarchy: create a constraint to prevent circularity through foreign keys 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:


Is it possible to write a constraint that checks this? and 
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 ;)
I have seen 2 main ways of enforcing this:
1, the OLD way:
CREATE TABLE Foo 
    (FooId BIGINT PRIMARY KEY,
     ParentFooId BIGINT,
     FooHierarchy VARCHAR(256),
     FOREIGN KEY([ParentFooId]) REFERENCES Foo ([FooId]) )

The FooHierarchy column would contain a value like this:
&quot;|1|27|425&quot;

Where the numbers map to the FooId column.  You would then enforce that the Hierarchy column ends with &quot;|id&quot; and the rest of the string matches the FooHieratchy of the PARENT.
2, the NEW way:
SQL Server 2008 has a new datatype called the HierarchyID, that does all of this for you.
It operates on the same principal as the OLD way, but it is handled efficiently by SQL Server, and is suitable for use as a REPLACEMENT for your &quot;ParentID&quot; column.
CREATE TABLE Foo 
    (FooId BIGINT PRIMARY KEY,
     FooHierarchy HIERARCHYID )