On Sat, Jul 27, 2002 at 07:07:13PM -0400, Tom Lane wrote: > [EMAIL PROTECTED] (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 <[EMAIL PROTECTED]> PGP Key ID: DB3C29FC ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
