Re: Alternative to Select in table check constraint

> > The following codes doesn't work on PostgreSQL 8.1.4 but according to
> > the book does conform to SQL-92.
> 
> >         CHECK   ( 1 = ALL (     SELECT COUNT(STATUS)
> >                                 FROM BADGES
> >                                 WHERE STATUS = 'A'
> >                                 GROUP BY EMPNO))
> 
> Yeah, the spec does allow sub-SELECTs in CHECK constraints but PG
> doesn't implement that.  The problem with it is that there's no clear
> way to make it perform reasonably, because the CHECK doesn't simply
> implicate the row you're currently inserting/updating --- every other
> row is potentially referenced by the sub-SELECT, and so changing row
> X might make the CHECK condition fail at row Y.  A brute-force
> implementation would be that every update of any sort to BADGES causes
> us to re-evaluate the CHECK constraint (hence, re-scan BADGES entirely
> to compute the sub-SELECT) for every row of BADGES (hence, O(N^2) work
> if there are N rows in the table).  That is certainly unworkable :-(.
> A bright person can think of ways to optimize particular cases but
> it's not easy to see how the machine might figure it out for arbitrary
> SELECTs.
> 
> The unique-index hack that Michael suggested amounts to hand-optimizing
> the sub-SELECT constraint into something that's efficiently checkable.
> 
>             regards, tom lane
Ah.  Thanks for the clarification.

Regards,

Richard Broersma Jr.