SQL Sub queries in check constraint

by

It is not supported to look beyond the current row in a CHECK constraint.

http://www.postgresql.org/docs/9.1/interactive/sql-createtable.html says:

A check constraint specified as a column constraint should reference
that column’s value only, while an expression appearing in a table
constraint can reference multiple columns.

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

There are good reasons for this restriction, but if you like to juggle flaming torches while riding a unicycle through heavy traffic, you can subvert the restriction using functions. The situations in which this will not come back to bite you are rare; you would be much safer to enforce the invariant in trigger code instead.

http://www.postgresql.org/docs/9.1/interactive/triggers.html

More Related Contents:

  1. What are ‘$$’ used for in PL/pgSQL
  2. INSERT with dynamic table name in trigger function
  3. The forgotten assignment operator “=” and the commonplace “:=”
  4. Truncating all tables in a Postgres database
  5. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  6. How to set value of composite variable field using dynamic SQL
  7. How do I do large non-blocking updates in PostgreSQL?
  8. Execute a dynamic crosstab query
  9. Is there a way to disable function overloading in Postgres
  10. Can I make a plpgsql function return an integer without using a variable?
  11. PostgreSQL function returning multiple result sets
  12. Dynamic SQL (EXECUTE) as condition for IF statement
  13. Returning from a function with OUT parameter
  14. SELECT raises exception in PL/pgSQL function
  15. Test for null in function with varying parameters
  16. Return SETOF rows from PostgreSQL function
  17. Error when setting n_distinct using a plpgsql variable
  18. Postgres FOR LOOP
  19. Dynamically generate columns for crosstab in PostgreSQL
  20. Function to loop through and select data from multiple tables
  21. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  22. Cursor based records in PostgreSQL
  23. ERROR: unterminated quoted string at or near
  24. How can I send some http request from postgresql function or trigger
  25. Select from a table variable
  26. Hashing a String to a Numeric Value in PostgreSQL
  27. Loop on tables with PL/pgSQL in Postgres 9.0+
  28. plpgsql error “RETURN NEXT cannot have a parameter in function with OUT parameters” in table-returning function
  29. Loop through columns of RECORD
  30. Format specifier for integer variables in format() for EXECUTE?

Leave a Comment