From: Andreas Joseph Krogh <andreak@officenet.no>
Organization: OfficeNet AS
To: pgsql-sql@postgresql.org
Subject: Re: Alternative to Select in table check constraint
Date: Fri, 7 Jul 2006 11:47:13 +0200
User-Agent: KMail/1.9.3
References: <20060701014108.79890.qmail@web31811.mail.mud.yahoo.com>
In-Reply-To: <20060701014108.79890.qmail@web31811.mail.mud.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200607071147.13400.andreak@officenet.no>

On Saturday 01 July 2006 03:41, Richard Broersma Jr wrote:
> > >         CHECK   ( 1 =3D ALL (     SELECT COUNT(STATUS)
> > >                                 FROM BADGES
> > >                                 WHERE STATUS =3D '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 =3D '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 actual=
ly
> worked), how would would it be possible to change the active status from
> one badge to another?

=46irst set status=3D'I' on the one that has status=3D'A', and set status=
=3D'A' on the=20
one you want.

=2D-=20
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
=2D-----------------------+---------------------------------------------+
OfficeNet AS            | The most difficult thing in the world is to |
Karenslyst All=E9 11      | know how to do a thing and to watch         |
PO. Box 529 Sk=F8yen      | somebody else doing it wrong, without       |
0214 Oslo               | comment.                                    |
NORWAY                  |                                             |
Mobile: +47 909  56 963 |                                             |
=2D-----------------------+---------------------------------------------+