Testing Nested Sets

Nate Johnson continues his nested set series:

Now the complex.  To validate that our tree is properly structured, the following statements need to be true:

1. Each node’s Right value is greater than its Left.

2. More to the point, each node’s Right value is greater than all of its ancestors’ Left values.

3. Similarly, each node’s Left value is less than all of its descendants’ Left values (and Right values, obviously!)

4. Leaf nodes have no gaps between Left & Right: Right = Left + 1

5. Depth is easy to verify because we already wrote the rCTE to calculate it!

6. And of course, no orphans – all ParentIDs lead to an actual parent node, except of course if they’re NULL (root nodes).

Read on for further explanation of these points.