How To Prevent Tree Having Circular References

original table is more complicated but.. i got a table which stores great many trees inside like; what im looking is a nontrigger mothod like constraint or a trigger which decides

Solution 1:

You can check if circular references or not by bellow query:

createtrigger check_circular_ref_tgr on myTreeTable forinsert, updateasbegindeclare@new_node varchar(80), @new_parent varchar(80)

select@new_node=node, @new_parent=parent from inserted

with p(id) as (
select parent from myTreeTable where node =@new_parent
unionallselect parent from myTreeTable innerjoin p on myTreeTable.node=p.id where parent isnotnull)

if exists(select id from p where id=@new_node)
  raiseerror(N'circular reference error', 10, 1)

end

Solution 2:

You could use an Instead of Trigger to override Updates and Inserts. See this SO post: How to prevent updates to a table, with an exception for one situation

Solution 3:

You can add a "Descendants" table where for each pair of nodes you record whether one node is a descendant of another. It is going to have way fewer than N*N rows, because you do not need entries for nodes that are completely unrelated. (Absence of an entry means "not a descendant".)

This will give the fastest performance when searching, but it will incur a performance penalty when inserting/deleting, because you will have to update the "Descendants" table.

Solution 4:

You could write a function that checks to see if a insert/change will create a circular reference and call it from a CHECK CONSTRAINT.