X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 775CB9FA5CD for ; Sat, 1 Jul 2006 17:36:57 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 69747-04 for ; Sat, 1 Jul 2006 17:36:54 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.185]) by postgresql.org (Postfix) with ESMTP id A44509FA5C3 for ; Sat, 1 Jul 2006 17:36:53 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id c29so516508nfb for ; Sat, 01 Jul 2006 13:36:52 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=kGKp9WpzG07iFU+drvx89enLBIGvjOBVDCz+Kmq4opfOkcKGSmNYgC9RxiWJixTrrCB7MoFlBAEo4dML2cTpwNzabDFM1gtwRUGIqHjNPwBWHTcXRRoutHp+XvSUEkubdFESaRM93vDncBlt81PxglWGiLkpRyH62k89WJNJzoE= Received: by 10.78.177.3 with SMTP id z3mr1895889hue; Sat, 01 Jul 2006 13:36:52 -0700 (PDT) Received: by 10.78.29.2 with HTTP; Sat, 1 Jul 2006 13:36:51 -0700 (PDT) Message-ID: Date: Sat, 1 Jul 2006 15:36:51 -0500 From: "Aaron Bono" To: "Richard Broersma Jr" Subject: Re: Alternative to Select in table check constraint Cc: "Michael Glaesemann" , "General Postgresql List" In-Reply-To: <20060701014108.79890.qmail@web31811.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_25063_12532427.1151786211939" References: <4FA18A22-AFC2-4C6A-A487-57DAEF09F59F@seespotcode.net> <20060701014108.79890.qmail@web31811.mail.mud.yahoo.com> X-Google-Sender-Auth: 5f7b913bf4537b24 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/9 X-Sequence-Number: 25583 ------=_Part_25063_12532427.1151786211939 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline This is more of an implementation option, but when I worry about what is active/inactive I put start/end dates on the tables. Then you don't need active indicators. You just look for the record where now() is >= start date and now() <= end date or end date is null. You can even activate/deactivate a badge on a future date. Of course, this complicates the data integrity - you will need some kind of specialized trigger that checks the data and makes sure there are no date overlaps to ensure you don't have two badges active at the same time. But is also gives you a history of badges and their activities. -Aaron On 6/30/06, 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? > > Oh well, partial index are obvious the superior solution since the entire > table doesn't not have > to be scanned to determine if the new badge can be set to active. > > Once again thanks for the insight. > > Regards, > > Richard Broersma Jr. > > ------=_Part_25063_12532427.1151786211939 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline This is more of an implementation option, but when I worry about what is active/inactive I put start/end dates on the tables.  Then you don't need active indicators.  You just look for the record where now() is >= start date and now() <= end date or end date is null.  You can even activate/deactivate a badge on a future date.  Of course, this complicates the data integrity - you will need some kind of specialized trigger that checks the data and makes sure there are no date overlaps to ensure you don't have two badges active at the same time.  But is also gives you a history of badges and their activities.

-Aaron

On 6/30/06, Richard Broersma Jr <rabroersma@yahoo.com> 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?

Oh well, partial index are obvious the superior solution since the entire table doesn't not have
to be scanned to determine if the new badge can be set to active.

Once again thanks for the insight.

Regards,

Richard Broersma Jr.

------=_Part_25063_12532427.1151786211939--