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 references 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.
1
1) Can you explain better your check constraint. What exactly you want to check? Circles?
If you going to check the whole path for item on INSERT this can cost good time … each jump means indexes search of a key.
2) I do not see how you can make ‘parent’ to be a virtual column, i.e. calculated column … It will be calculated from where?
I think the ‘parent’ column is required to save on disk link to a parent item.