Thread: sub-selects in CHECK

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? From a (very) brief look at execMain.c, ExecEvalExpr() seems
to support subplans already, so I wouldn't *guess* it would be too
involved, but I'd appreciate a more informed assessment...

Thanks in advance,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

nconway@klamath.dyndns.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.
        regards, tom lane

On Sat, Jul 27, 2002 at 07:07:13PM -0400, Tom Lane wrote:
> nconway@klamath.dyndns.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@rogers.com>
PGP Key ID: DB3C29FC

nconway@klamath.dyndns.org (Neil Conway) writes:
> 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...

It's effectively equivalent to a database-wide assertion, which is
another SQL feature that we don't support.

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

Certainly, but this one isn't really ill-defined, it's just very
difficult to support in any acceptably-efficient manner.

If you want to cheat horribly, ie have the condition checked only when a
single-row constraint would be checked, then you can stick the subselect
inside a function call.  I don't think we are really adding any
functionality unless we can do better than that.
        regards, tom lane