Re: Alternative to Select in table check constraint

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