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 ;)