From: Andreas Joseph Krogh <andreak@officenet.no>
To: pgsql-sql@postgresql.org
Subject: Re: Alternative to Select in table check constraint
Date: Fri, 7 Jul 2006 11:47:13 +0200
Message-ID: <200607071147.13400.andreak@officenet.no> (raw)
In-Reply-To: <20060701014108.79890.qmail@web31811.mail.mud.yahoo.com>
References: <20060701014108.79890.qmail@web31811.mail.mud.yahoo.com>
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 | |
------------------------+---------------------------------------------+
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreak@officenet.no
Subject: Re: Alternative to Select in table check constraint
In-Reply-To: <200607071147.13400.andreak@officenet.no>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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