Say we want to enforce that a table represents a tree. I have found this method for doing so:
create table tree ( id integer primary key, parent integer references tree (id), item text not null, constraint tree_parent check ( (id = array::integer and parent is null) or parent = id[1:array_length(id,1)-1]) );
A node in the tree is identified by its path from the root node. As such, the foreign key
parent is just that path with one element dropped off the end. The combination of
foreign key and
check guarantees the tree structure.
First question: is this a good idea? Are there better approaches? In Oracle at least, I'm able to define
parent as a virtual column, which is elegant.
Second question: is there a name for this approach? I like to think of this as a "functional foreign key":
tree.id, but via a function that drops the last element in the path. I think this concept has more general use than the tree example above.