CHECK (table1.integer >= table2.integer)

I need to create a CHECK constraint to verify that the entered integer in a column is greater than or equal to the integer in another column in a different table.

For example, the following tables would be valid:

=# SELECT * FROM table1;
 current_project_number
------------------------
                     12

=# SELECT * FROM table2;
 project_name | project_number
--------------+----------------
 Schaf        |              1
 Hase         |              8
 Hai          |             12

And the following tables would NOT be valid:

=# SELECT * FROM table1;
 current_project_number
------------------------
                     12

=# SELECT * FROM table2;
 project_name | project_number
--------------+----------------
 Schaf        |              1
 Hase         |              8
 Hai          |             12
 Erdmännchen  |             71    <-error:table1.current_project_number is NOT >= 71

Please note this CHECK constraint is designed to make sure info like above cannot be inserted. I'm not looking to SELECT values where current_project_number >= project_number, this is about INSERTing

What would I need in order for such a CHECK to work? Thanks

Solution

Defining a CHECK constraint that references another table is possible, but a seriously bad idea that will lead to problems in the future.

CHECK constraints are only validated when the table with the constraint on it is modified, not when the other table referenced in the constraint is modified. So it is possible to render the condition invalid with modifications on that second table.

In other words, PostgreSQL will not guarantee that the constraint is always valid. This can and will lead to unpleasant surprises, like a backup taken with pg_dump that can no longer be restored.

Don't go down that road.

If you need functionality like that, define a BEFORE INSERT trigger on table1 that verifies the condition and throws an exception otherwise:

CREATE FUNCTION ins_trig() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF EXISTS (SELECT 1 FROM table1
              WHERE NEW.project_number > current_project_number)
   THEN
      RAISE EXCEPTION 'project number must be less than or equal to values in table1';
   END IF;

   RETURN NEW;
END;$$;

CREATE TRIGGER ins_trig BEFORE INSERT ON table2
   FOR EACH ROW EXECUTE PROCEDURE ins_trig();