Re: sub-selects in CHECK

On Sat, Jul 27, 2002 at 07:07:13PM -0400, Tom Lane wrote:
> nconway(at)klamath(dot)dyndns(dot)org (Neil Conway) writes:
> > I'd like to add the ability to use a sub-select in a CHECK constraint.
> > Can someone elaborate on what changes would be needed to support
> > this?
>
> Define what you think should happen when the other rows referenced
> by the subselect change.

Good point -- but given that SQL99 specifically mentions that this
functionality should be available (Feature 671, "Subqueries in
CHECK constraints"), there must be some reasonable behavior
adopted by another DBMS...

In any case, there are already plenty of ways to create non-sensical
constraints. For example:

CHECK ( foo < random() )

or even:

CREATE FUNCTION check_func() returns int as 'select ...' language 'sql';

ALTER TABLE foo ADD CONSTRAINT check_x CHECK (x > check_func() );

(which is effectively a sub-select with a different syntax)

So the restrictions "no sub-selects or aggregates in a CHECK constraint"
is quite insufficient, if we actually want to prevent an application
developer from creating dubious constraints.

Cheers,

Neil

--
Neil Conway <neilconway(at)rogers(dot)com>
PGP Key ID: DB3C29FC