From: Richard Broersma Jr <rabroersma@yahoo.com>
To: Rod Taylor <pg@rbt.ca>
Cc: Michael Glaesemann <grzm@seespotcode.net>
Cc: General Postgresql List <pgsql-sql@postgresql.org>
Subject: Re: Alternative to Select in table check constraint
Date: Sun, 2 Jul 2006 18:50:25 -0700 (PDT)
Message-ID: <20060703015025.2191.qmail@web31815.mail.mud.yahoo.com> (raw)
In-Reply-To: <1151773591.673.87.camel@home>
> 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.
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: rabroersma@yahoo.com, pg@rbt.ca, grzm@seespotcode.net
Subject: Re: Alternative to Select in table check constraint
In-Reply-To: <20060703015025.2191.qmail@web31815.mail.mud.yahoo.com>
* 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