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