DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=t4AlVGAz2YcbLaGU3JzFaRxNnWBKPeWxfhOCR9qwgz9e1ntVJNBuUHuW/CHlf5Vh727/kLFAKpX/XqtUZ5jnqT7u8aO7J6sBAfna45hnx5zY/hQev0C56E+f4Qf8kCBV6C0UQ8V1mcLBJ4Q7dTFVn/V/GpP396wMMIh8xTNI7lc=
	;
Message-ID: <20060703015025.2191.qmail@web31815.mail.mud.yahoo.com>
Date: Sun, 2 Jul 2006 18:50:25 -0700 (PDT)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: Alternative to Select in table check constraint
To: Rod Taylor <pg@rbt.ca>
Cc: Michael Glaesemann <grzm@seespotcode.net>,
	General Postgresql List <pgsql-sql@postgresql.org>
In-Reply-To: <1151773591.673.87.camel@home>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

> 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.