pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAlternative to Select in table check constraint
11+ messages / 7 participants
[nested] [flat]
* Alternative to Select in table check constraint
@ 2006-06-30 21:55 Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 2 replies; 11+ messages in thread
From: Richard Broersma Jr @ 2006-06-30 21:55 UTC (permalink / raw)
To: pgsql-sql
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))
);
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alternative to Select in table check constraint
@ 2006-07-01 00:27 Michael Glaesemann <grzm@seespotcode.net>
parent: Richard Broersma Jr <rabroersma@yahoo.com>
1 sibling, 1 reply; 11+ messages in thread
From: Michael Glaesemann @ 2006-07-01 00:27 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alternative to Select in table check constraint
@ 2006-07-01 01:41 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 4 replies; 11+ messages in thread
From: Richard Broersma Jr @ 2006-07-01 01:41 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: pgsql-sql
> > 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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alternative to Select in table check constraint
@ 2006-07-01 04:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Broersma Jr <rabroersma@yahoo.com>
1 sibling, 1 reply; 11+ messages in thread
From: Tom Lane @ 2006-07-01 04:11 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alternative to Select in table check constraint
@ 2006-07-01 05:10 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 11+ messages in thread
From: Richard Broersma Jr @ 2006-07-01 05:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
> > 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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alternative to Select in table check constraint
@ 2006-07-01 12:54 Erik Jones <erik@myemma.com>
parent: Richard Broersma Jr <rabroersma@yahoo.com>
3 siblings, 0 replies; 11+ messages in thread
From: Erik Jones @ 2006-07-01 12:54 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alternative to Select in table check constraint
@ 2006-07-01 17:06 Rod Taylor <pg@rbt.ca>
parent: Richard Broersma Jr <rabroersma@yahoo.com>
3 siblings, 1 reply; 11+ messages in thread
From: Rod Taylor @ 2006-07-01 17:06 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: Michael Glaesemann <grzm@seespotcode.net>; pgsql-sql
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.
--
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alternative to Select in table check constraint
@ 2006-07-01 20:36 Aaron Bono <postgresql@aranya.com>
parent: Richard Broersma Jr <rabroersma@yahoo.com>
3 siblings, 1 reply; 11+ messages in thread
From: Aaron Bono @ 2006-07-01 20:36 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: Michael Glaesemann <grzm@seespotcode.net>; pgsql-sql
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
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.
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alternative to Select in table check constraint
@ 2006-07-03 01:50 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Rod Taylor <pg@rbt.ca>
0 siblings, 0 replies; 11+ messages in thread
From: Richard Broersma Jr @ 2006-07-03 01:50 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; +Cc: Michael Glaesemann <grzm@seespotcode.net>; pgsql-sql
> 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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alternative to Select in table check constraint
@ 2006-07-03 01:52 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Aaron Bono <postgresql@aranya.com>
0 siblings, 0 replies; 11+ messages in thread
From: Richard Broersma Jr @ 2006-07-03 01:52 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: Michael Glaesemann <grzm@seespotcode.net>; pgsql-sql
> 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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alternative to Select in table check constraint
@ 2006-07-07 09:47 Andreas Joseph Krogh <andreak@officenet.no>
parent: Richard Broersma Jr <rabroersma@yahoo.com>
3 siblings, 0 replies; 11+ messages in thread
From: Andreas Joseph Krogh @ 2006-07-07 09:47 UTC (permalink / raw)
To: pgsql-sql
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 | |
------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2006-07-07 09:47 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-30 21:55 Alternative to Select in table check constraint Richard Broersma Jr <rabroersma@yahoo.com>
2006-07-01 00:27 ` Michael Glaesemann <grzm@seespotcode.net>
2006-07-01 01:41 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-07-01 12:54 ` Erik Jones <erik@myemma.com>
2006-07-01 17:06 ` Rod Taylor <pg@rbt.ca>
2006-07-03 01:50 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-07-01 20:36 ` Aaron Bono <postgresql@aranya.com>
2006-07-03 01:52 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-07-07 09:47 ` Andreas Joseph Krogh <andreak@officenet.no>
2006-07-01 04:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-01 05:10 ` Richard Broersma Jr <rabroersma@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox