Subject Context variables in CHECK constraints
Author Marco Bommeljé
Hello everyone (especially Helen Borrie),

In a recent discussion on Transaction Isolation levels, Helen wrote:

> You can *write* a check constraint that reads a value in another
> table but it's a practice I strongly advise against.

and later in the same thread:

> .. a subquery on another table? (the one I recommended not to do)?
> Something like
> Alter table add constraint
> check(new.some_flag in (select flags.flag from flags))

I have two questions about this:
What is so bad about using subqueries in CHECK constraints (provided
that they only SELECT on FK referenced tables)?

In my opinion, subqueries in CHECK constraints adds expressive power to
the constraints. Not using this possibility would limit the use of CHECK
constraints to the simplest checking of row values like in Oracle.

What is wrong with using the NEW and OLD context variables in CHECK
constraints? I realize that it isn't documented, but it seems to work.

Some existing database implementations rely on these features, so I like
to know in what kind of mess I am in.