CONSTRAINT to check values from a remotely related table (via join etc.)

by

CHECK constraints cannot currently reference other tables. The manual:

Currently, CHECK expressions cannot contain subqueries nor refer to
variables other than columns of the current row.

One way is to use a trigger like demonstrated by @Wolph.

A clean solution without triggers: add redundant columns and include them in FOREIGN KEY constraints, which are the first choice to enforce referential integrity. Related answer on dba.SE with detailed instructions:

Another option would be to “fake” an IMMUTABLE function doing the check and use that in a CHECK constraint. Postgres will allow this, but be aware of possible caveats. Best make that a NOT VALID constraint. See:

More Related Contents:

  1. Does a Postgres UNIQUE constraint imply an index?
  2. Calculating and saving space in PostgreSQL
  3. Create unique constraint with null columns
  4. What’s wrong with foreign keys?
  5. Store common query as column?
  6. How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
  7. Postgresql: Conditionally unique constraint
  8. Making sense of Postgres row sizes
  9. Do nullable columns occupy additional space in PostgreSQL?
  10. Does not using NULL in PostgreSQL still use a NULL bitmap in the header?
  11. How to add “on delete cascade” constraints?
  12. Grant all on a specific schema in the db to a group role in PostgreSQL
  13. Delete parent if it’s not referenced by any other child
  14. Why can I create a table with PRIMARY KEY on a nullable column?
  15. Postgres constraint for unique datetime range
  16. PostgreSQL array of elements that each are a foreign key
  17. PostgreSQL – disabling constraints
  18. Composite PRIMARY KEY enforces NOT NULL constraints on involved columns
  19. PostgreSQL – set a default cell value according to another cell value
  20. How does the search_path influence identifier resolution and the “current schema”
  21. How do you find the row count for all your tables in Postgres
  22. How do you use variables in a simple PostgreSQL script?
  23. How to switch databases in psql?
  24. Which is a good design for a database table that can be owned by two different resources, and therefore needs two different foreign keys? [closed]
  25. How can I change a PostgreSQL user password?
  26. I forgot the password I entered during PostgreSQL installation
  27. SQL query column does not exist error
  28. Temporal database design, with a twist (live vs draft rows)
  29. What’s the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)
  30. JPA and PostgreSQL with GenerationType.IDENTITY

Leave a Comment