Check constraint to guarantee ltree ancestors

Question

I ran into an issue where we had a tree structure that looked like this:

select title, tree_path from my_table where tree_path <@ '3';

title    | tree_path
-----------------------
item A   | 3
item B   | 3.1.1
item C   | 3.1.2
item D   | 3.1.3

I somehow lost anything at 3.1 so all tree_path <@ '3.1' were orphaned.

Is there some sort of constraint I can write that would ensure that a node's ancestors exist?

Answer 1

Since this is a tree structure, you can use a "parent" field with a FOREIGN KEY reference to the table itself, as described in Adjacency list vs. nested sets by Quassnoi.

For example, if you had another item E with tree_path = 3.1, then the parent values of B, C, D would be E. The parent of E would be A. The parent of A would be NULL.

If you delete E, then enforcing the foreign key constraint would require a cascaded deletion of the children B, C, D.

Answer 2

Yes, you can easily add a virtual or auto updated field, such as parent_id.

Assuming the numbers in your path are referencing the id column of the ltree table, so for a table with id, title, and path columns, you can add parent_id with a trigger that can automatically keepit up to date. Then you can add a constraint on parent_id to id with On Delete Restrict.

Your trigger function would look like this:

BEGIN
  IF nlevel(new.path) > 1 then
    new.parent_id = ltree2text(subpath(new.path,-2,1))::bigint;
  end if; 
  RETURN NEW;
END;