In-Reply-To: <20060630215512.18287.qmail@web31813.mail.mud.yahoo.com>
References: <20060630215512.18287.qmail@web31813.mail.mud.yahoo.com>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <4FA18A22-AFC2-4C6A-A487-57DAEF09F59F@seespotcode.net>
Cc: General Postgresql List <pgsql-sql@postgresql.org>
Content-Transfer-Encoding: 7bit
From: Michael Glaesemann <grzm@seespotcode.net>
Subject: Re: Alternative to Select in table check constraint
Date: Sat, 1 Jul 2006 09:27:07 +0900
To: Richard Broersma Jr <rabroersma@yahoo.com>


On Jul 1, 2006, at 6:55 , 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)

> Currently, CHECK expressions cannot contain subqueries nor refer to  
> variables other than columns of the current row.

This is why the the above won't work. You can probably do something  
similar by creating UNIQUE index with a WHERE clause. For example (if  
I'm understanding the intent),

CREATE UNIQUE INDEX one_a_badge_per_employee_idx
ON badges (empno)
WHERE status = 'A';

Here are links to more documentation on indexes:

http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html
http://www.postgresql.org/docs/8.1/interactive/indexes-partial.html

Hope this helps.

Michael Glaesemann
grzm seespotcode net