Re: [SQL] Alternative to Select in table check constraint

[Michael Glaesemann]

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




---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

              http://archives.postgresql.org