Prevent inserting wrong parent id

Question

I have two tables in a Postgres 17 database: site and page. Each site can have multiple pages and each page can have a parent page:

CREATE TABLE site (
  id INT PRIMARY KEY
);
CREATE TABLE page (
  id INT PRIMARY KEY,
  site_id INT references site(id),
  parent_id INT references page(id)
);

Now suppose I have two sites:

INSERT INTO site (id) VALUES (1);
INSERT INTO site (id) VALUES (2);

And one root page for each site:

INSERT INTO page (id, site_id, parent_id) VALUES (1, 1, NULL);
INSERT INTO page (id, site_id, parent_id) VALUES (2, 2, NULL);

How to prevent inserting a new page for site 2 with a parent page which belongs to site 1?

INSERT INTO page (id, site_id, parent_id) VALUES (3, 2, 1);

Erwin Brandstetter · Accepted Answer · 2024-11-02T19:12:29.523

To enforce that the parent page must point to the same site:

CREATE TABLE site (
  id int PRIMARY KEY
);
CREATE TABLE page (
  id        int PRIMARY KEY
, site_id   int NOT NULL
, parent_id int
, CONSTRAINT page_site_id_fk FOREIGN KEY (site_id) REFERENCES site
, CONSTRAINT page_site_id_parent_id_uniq UNIQUE (id, site_id)  -- needed for following FK
, CONSTRAINT page_hierarchy_fk FOREIGN KEY (parent_id, site_id) REFERENCES page (id, site_id)
, CONSTRAINT page_no_self_rel CHECK (parent_id <> id)  -- disallow self-reference
);

fiddle

The multicolumn FK constraint page_hierarchy_fk adds site_id to the reference, so the parent must point to the same site. This FK requires an otherwise redundant UNIQUE constraint on page(id, site_id).

I also threw in a CHECK constraint to disallow self references. Now you cannot create circles with single-row inserts. But you still can create circles over two or more hubs with multi-row inserts or with an UPDATE. If that is a problem, create a (potentially expensive) trigger that walks the graph on insert / update to rule out endless loops.

Prevent inserting wrong parent id

1 Answers1