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.