2

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?

3 Answers 3

5

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.

2
  • Thanks. Is there a way to make the parent field a virtual field calculated off of the tree so I don't have to maintain the hierarchy in two places?
    – lorddev
    Commented Jun 16, 2021 at 14:59
  • @lorddev I haven't used virtual fields myself, but I guess you can do the opposite -- make the tree_path a virtual field computed from parent. In the linked article, there are examples of how to find all ancestors of a particular node, which, if accumulated with an aggregate function, renders the path.
    – tinlyx
    Commented Jun 16, 2021 at 21:16
0

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;
0

Inspired by Paul's answer, I used the following steps to solve this problem.

  1. Add a generated column to keep track of the group's parent.
ALTER TABLE my_table 
ADD COLUMN parent_path ltree GENERATED ALWAYS AS (
    CASE 
        WHEN nlevel("tree_path") > 1 THEN subpath("tree_path", 0, nlevel("tree_path") - 1)
        ELSE NULL
    END
) STORED;

  1. Created a foreign key constraint on that same column referencing the same table.
ALTER TABLE my_table 
ADD CONSTRAINT fk_my_table_parent FOREIGN KEY (parent_path) REFERENCES my_table("tree_path");

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.