From: | Andreas Joseph Krogh <andreak(at)officenet(dot)no> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Alternative to Select in table check constraint |
Date: | 2006-07-07 09:47:13 |
Message-ID: | 200607071147.13400.andreak@officenet.no |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)officenet(dot)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 | |
------------------------+---------------------------------------------+
From | Date | Subject | |
---|---|---|---|
Next Message | Gary Stainburn | 2006-07-07 10:29:35 | Update from join |
Previous Message | Aaron Bono | 2006-07-07 08:44:05 | Re: Select CASE Concatenation |