0

How to create a constraint check which ensures that parent_id column doesn't contain any element of child_id column ?

I tried this:

ALTER TABLE myschema.mytable
ADD CONSTRAINT parent_cannot_be_child
CHECK (
  child_id NOT IN (
    SELECT parent_id FROM myschema.mytable
  )
)

But I get this error message:

ERROR:  cannot use subquery in check constraint
DevonDahon
  • 7,460
  • 6
  • 69
  • 114

1 Answers1

0

In PostgreSQL, CHECK constraints cannot include subqueries. However, you can enforce this constraint using a trigger. A trigger is a function that gets executed either before or after an event (INSERT, UPDATE, DELETE) occurs on a row. Here is a basic implementation:

CREATE OR REPLACE FUNCTION check_parent_child() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.child_id = ANY (SELECT parent_id FROM myschema.mytable) THEN
    RAISE EXCEPTION 'Child id cannot be a parent id.';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_parent_child_trigger
BEFORE INSERT OR UPDATE ON myschema.mytable
FOR EACH ROW EXECUTE FUNCTION check_parent_child();
Humza Tareen
  • 146
  • 6