Re: Alternative to Select in table check constraint - Mailing list pgsql-sql

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