Re: Alternative to Select in table check constraint

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