DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=sxSa8CpRA1n9+8m9Wz5Hi6tjV8850m7rE90J8y4v1hlZylaZQ322bOFlYkUdPm1hc9k7u6NyTFlqupqviN695ICzDMyaqATbXc4Sr16rt4h8MKkzc3FQXCzjlRNKwcdm3QHfDblA6xYWG2EtSZjpsPugsrYcjECA7dAddGOFFrU=
	;
Message-ID: <20060630215512.18287.qmail@web31813.mail.mud.yahoo.com>
Date: Fri, 30 Jun 2006 14:55:12 -0700 (PDT)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Alternative to Select in table check constraint
To: General Postgresql List <pgsql-sql@postgresql.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

I am practicing with SQL examples comming from the book:

JOE CELKO'S
SQL
PUZZLES
& ANSWERS

The following codes doesn't work on PostgreSQL 8.1.4 but according to the book does conform to
SQL-92.  Is there any other solutions that would result in the same effect?  Or is this an example
of a contraint that should be avoided at all costs?

CREATE TABLE BADGES
(
        BADGENO         SERIAL NOT NULL PRIMARY KEY,
        EMPNO           INTEGER NOT NULL REFERENCES SECEMPLOYEES (EMPNO),
        ISSUEDATE       DATE NOT NULL,
        STATUS          CHAR(1) NOT NULL CHECK ( STATUS IN ('A', 'I')),

        CHECK   ( 1 = ALL (     SELECT COUNT(STATUS)
                                FROM BADGES
                                WHERE STATUS = 'A'
                                GROUP BY EMPNO))
);