From: Michael Glaesemann <grzm@seespotcode.net>
To: Richard Broersma Jr <rabroersma@yahoo.com>
Cc: General Postgresql List <pgsql-sql@postgresql.org>
Subject: Re: Alternative to Select in table check constraint
Date: Sat, 1 Jul 2006 09:27:07 +0900
Message-ID: <4FA18A22-AFC2-4C6A-A487-57DAEF09F59F@seespotcode.net> (raw)
In-Reply-To: <20060630215512.18287.qmail@web31813.mail.mud.yahoo.com>
References: <20060630215512.18287.qmail@web31813.mail.mud.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.htmlhttp://www.postgresql.org/docs/8.1/interactive/indexes-partial.html
Hope this helps.
Michael Glaesemann
grzm seespotcode net
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: grzm@seespotcode.net, rabroersma@yahoo.com
Subject: Re: Alternative to Select in table check constraint
In-Reply-To: <4FA18A22-AFC2-4C6A-A487-57DAEF09F59F@seespotcode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox