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.