Thread: Alternative to Select in table check constraint
I am practicing with SQL examples comming from the book:
JOE CELKO'S
SQL
PUZZLES
& ANSWERS
The following codes doesn't work on PostgreSQL 8.1.4 but according to the book does conform to
SQL-92. Is there any other solutions that would result in the same effect? Or is this an example
of a contraint that should be avoided at all costs?
CREATE TABLE BADGES
( BADGENO SERIAL NOT NULL PRIMARY KEY, EMPNO INTEGER NOT NULL REFERENCES SECEMPLOYEES
(EMPNO), ISSUEDATE DATE NOT NULL, STATUS CHAR(1) NOT NULL CHECK ( STATUS IN ('A', 'I')),
CHECK ( 1 = ALL ( SELECT COUNT(STATUS) FROM BADGES
WHERE STATUS = 'A' GROUP BY EMPNO))
);
On Jul 1, 2006, at 6:55 , Richard Broersma Jr wrote: > CHECK ( 1 = ALL ( SELECT COUNT(STATUS) > FROM BADGES > WHERE STATUS = 'A' > GROUP BY EMPNO)) From the PostgreSQL docs (http://www.postgresql.org/docs/8.1/ interactive/sql-createtable.html) > Currently, CHECK expressions cannot contain subqueries nor refer to > variables other than columns of the current row. This is why the the above won't work. You can probably do something similar by creating UNIQUE index with a WHERE clause. For example (if I'm understanding the intent), CREATE UNIQUE INDEX one_a_badge_per_employee_idx ON badges (empno) WHERE status = 'A'; Here are links to more documentation on indexes: http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html http://www.postgresql.org/docs/8.1/interactive/indexes-partial.html Hope this helps. Michael Glaesemann grzm seespotcode net
> > CHECK ( 1 = ALL ( SELECT COUNT(STATUS) > > FROM BADGES > > WHERE STATUS = 'A' > > GROUP BY EMPNO)) > > From the PostgreSQL docs (http://www.postgresql.org/docs/8.1/ > interactive/sql-createtable.html) > > CREATE UNIQUE INDEX one_a_badge_per_employee_idx > ON badges (empno) > WHERE status = 'A'; > http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html > http://www.postgresql.org/docs/8.1/interactive/indexes-partial.html Michael, Partial indexs seem to be "what the doctor ordered!" And your suggest is right on, the idea of the constraint is to allow only one active badge status at a time. But now that I think about it, using the authors suggestion (if it actually worked), how would would it be possible to change the active status from one badge to another? Oh well, partial index are obvious the superior solution since the entire table doesn't not have to be scanned to determine if the new badge can be set to active. Once again thanks for the insight. Regards, Richard Broersma Jr.
Richard Broersma Jr <rabroersma@yahoo.com> writes:
> 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
> > 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.
Richard Broersma Jr wrote: > But now that I think about it, using the authors suggestion (if it actually worked), how would > would it be possible to change the active status from one badge to another? > Well, the check constraint as you wrote it requires that there always be a badge listed as active for each employee. If you changed the contstraint to: CHECK ( 2 > .....) then you'd be able to unset the active status and then set a new one to active. -- erik jones <erik@myemma.com> software development emma(r)
On Fri, 2006-06-30 at 18:41 -0700, Richard Broersma Jr wrote: > > > CHECK ( 1 = ALL ( SELECT COUNT(STATUS) > > > FROM BADGES > > > WHERE STATUS = 'A' > > > GROUP BY EMPNO)) > > > > From the PostgreSQL docs (http://www.postgresql.org/docs/8.1/ > > interactive/sql-createtable.html) > > > > CREATE UNIQUE INDEX one_a_badge_per_employee_idx > > ON badges (empno) > > WHERE status = 'A'; > > http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html > > http://www.postgresql.org/docs/8.1/interactive/indexes-partial.html > > Michael, > > Partial indexs seem to be "what the doctor ordered!" And your suggest is right on, the idea of > the constraint is to allow only one active badge status at a time. > > But now that I think about it, using the authors suggestion (if it actually worked), how would > would it be possible to change the active status from one badge to another? Unset the status first then set on the new one. Same transaction of course. You may find this type of constraint is more workable with a TRIGGER deferred until commit time than a unique constraint which cannot (at this time in PostgreSQL) be deferred. --
This is more of an implementation option, but when I worry about what is active/inactive I put start/end dates on the tables. Then you don't need active indicators. You just look for the record where now() is >= start date and now() <= end date or end date is null. You can even activate/deactivate a badge on a future date. Of course, this complicates the data integrity - you will need some kind of specialized trigger that checks the data and makes sure there are no date overlaps to ensure you don't have two badges active at the same time. But is also gives you a history of badges and their activities.
-Aaron
-Aaron
On 6/30/06, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> > CHECK ( 1 = ALL ( SELECT COUNT(STATUS)
> > FROM BADGES
> > WHERE STATUS = 'A'
> > GROUP BY EMPNO))
>
> From the PostgreSQL docs (http://www.postgresql.org/docs/8.1/
> interactive/sql-createtable.html)
>
> CREATE UNIQUE INDEX one_a_badge_per_employee_idx
> ON badges (empno)
> WHERE status = 'A';
> http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html
> http://www.postgresql.org/docs/8.1/interactive/indexes-partial.html
Michael,
Partial indexs seem to be "what the doctor ordered!" And your suggest is right on, the idea of
the constraint is to allow only one active badge status at a time.
But now that I think about it, using the authors suggestion (if it actually worked), how would
would it be possible to change the active status from one badge to another?
Oh well, partial index are obvious the superior solution since the entire table doesn't not have
to be scanned to determine if the new badge can be set to active.
Once again thanks for the insight.
Regards,
Richard Broersma Jr.
> On Fri, 2006-06-30 at 18:41 -0700, Richard Broersma Jr wrote: > > > > CHECK ( 1 = ALL ( SELECT COUNT(STATUS) > > > > FROM BADGES > > > > WHERE STATUS = 'A' > > > > GROUP BY EMPNO)) > > > > > > From the PostgreSQL docs (http://www.postgresql.org/docs/8.1/ > > > interactive/sql-createtable.html) > > > > > > CREATE UNIQUE INDEX one_a_badge_per_employee_idx > > > ON badges (empno) > > > WHERE status = 'A'; > > > http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html > > > http://www.postgresql.org/docs/8.1/interactive/indexes-partial.html > > > > Michael, > > > > Partial indexs seem to be "what the doctor ordered!" And your suggest is right on, the idea > of > > the constraint is to allow only one active badge status at a time. > > > > But now that I think about it, using the authors suggestion (if it actually worked), how would > > would it be possible to change the active status from one badge to another? > > Unset the status first then set on the new one. Same transaction of > course. > > You may find this type of constraint is more workable with a TRIGGER > deferred until commit time than a unique constraint which cannot (at > this time in PostgreSQL) be deferred. Thanks for the Input Rod. I will try implementing a trigger as a way to constrain the input data to see how it works. Regards, Richard Broersma Jr.
> This is more of an implementation option, but when I worry about what is > active/inactive I put start/end dates on the tables. Then you don't need > active indicators. You just look for the record where now() is >= start > date and now() <= end date or end date is null. You can even > activate/deactivate a badge on a future date. Of course, this complicates > the data integrity - you will need some kind of specialized trigger that > checks the data and makes sure there are no date overlaps to ensure you > don't have two badges active at the same time. But is also gives you a > history of badges and their activities. Good point. I take it that this type of solution stems from temporal schema design. Regards, Richard Broersma Jr.
On Saturday 01 July 2006 03:41, Richard Broersma Jr wrote: > > > CHECK ( 1 = ALL ( SELECT COUNT(STATUS) > > > FROM BADGES > > > WHERE STATUS = 'A' > > > GROUP BY EMPNO)) > > > > From the PostgreSQL docs (http://www.postgresql.org/docs/8.1/ > > interactive/sql-createtable.html) > > > > CREATE UNIQUE INDEX one_a_badge_per_employee_idx > > ON badges (empno) > > WHERE status = 'A'; > > http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html > > http://www.postgresql.org/docs/8.1/interactive/indexes-partial.html > > Michael, > > Partial indexs seem to be "what the doctor ordered!" And your suggest is > right on, the idea of the constraint is to allow only one active badge > status at a time. > > But now that I think about it, using the authors suggestion (if it actually > worked), how would would it be possible to change the active status from > one badge to another? First set status='I' on the one that has status='A', and set status='A' on the one you want. -- Andreas Joseph Krogh <andreak@officenet.no> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc ------------------------+---------------------------------------------+ OfficeNet AS | The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment. | NORWAY | | Mobile: +47 909 56 963 | | ------------------------+---------------------------------------------+