0

I'm trying to have a linked list-like (not actually, it's supposed to be a tree structure) in my database, and have a PostgresSQL CHECK that prevents cycles.

My data structure consists of records in a (id, parent_id, other_things) format. Is it possible to do this with a CHECK?

Edit: schema clarification My schema is as follows:

CREATE TABLE static_pages (
    id SERIAL PRIMARY KEY,
    parent_id INTEGER REFERENCES static_pages(id),
    other_things
);

And contains tuples like

(1, NULL),
(2, NULL),
(3, 1),
(4, 3),
(5, NULL)

et cetera.

Markus Malkusch
  • 7,738
  • 2
  • 38
  • 67
Bert Peters
  • 1,505
  • 13
  • 30

1 Answers1

1

No, you can't do it with a CHECK constraint, but a trigger can be used for this purpose.

Use a recursive CTE (WITH RECURSIVE) query to detect recursion in the trigger, starting with the NEW record in a trigger that fires BEFORE INSERT OR UPDATE ON thetable FOR EACH ROW EXECUTE PROCEDURE my_recursion_check().

If you add schema and sample data to your question then comment here I might have time to write a demo trigger.

See:

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778