how to Validate Hierarchy in a Nested Sets Model

Hello Experts,

I store Hierarchical data in SQL server using the Nested sets model.  After editing my tree (moves, adds, deletes...), how do I validate that the hierarchy in my Nested Sets Model is valid still?  I want to ensure that I don't lose any branches of my tree.

Who is Participating?
Your left-right can't have duplicates/gabs

You can also prevent deletes when depending data exists.
If you mean validate after insert/update -> walk through tree and eventualy
- report problems
- move loose ends to a 'loose end' hierarchy
OFGeminiAuthor Commented:

Yes, how do you walk through the tree?  When you walk through the tree wouldn't you only find 'not loose items' only?
OFGeminiAuthor Commented:
how do you query for gaps?
I am not a SQL expert but I doubt that finding a query for gaps is possible.

To check for the duplicates/gaps, what I would do is using a loop to fill all the the left-right values as keys of an array.
Then, using a second loop, I would check if each key is equal to the previous key +1.
In the end, the array should contain values = 2x the count() of the array and the first value should be 1.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.