X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 65778475DBC
	for <pgsql-sql@postgresql.org>; Tue, 25 Feb 2003 07:41:03 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 99322-09 for <pgsql-sql@postgresql.org>;
	Tue, 25 Feb 2003 07:40:50 -0500 (EST)
Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66])
	by postgresql.org (Postfix) with ESMTP id D2CB7476009
	for <pgsql-sql@postgresql.org>; Tue, 25 Feb 2003 07:10:30 -0500 (EST)
Received: from klaster.net (pq243.krakow.cvx.ppp.tpnet.pl [213.76.41.243])
	by serwer.skawsoft.com.pl (Postfix) with ESMTP
	id 51D592B896; Tue, 25 Feb 2003 13:09:54 +0100 (CET)
Message-ID: <3E5B53AD.8050406@klaster.net>
Date: Tue, 25 Feb 2003 12:29:49 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; PL; rv:1.1) Gecko/20020826
X-Accept-Language: pl, en-us, en
MIME-Version: 1.0
To: Rudi Starcevic <rudi@oasis.net.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: Sub Select inside Check ?
References: <3E5B03B9.4070500@oasis.net.au>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/545
X-Sequence-Number: 11943

Rudi Starcevic wrote:
> Hi,
> 
> I'm trying to create this table with a Check that fails.
> 
> I'm using 'Check' instead of 'References' due to poor db schema ( before 
> me .. )
> 
> Here is my error message:
> ERROR: cannot use subselect in CHECK constraint expression
> 
> CREATE TABLE cp_retailers
> (
> ret_id serial PRIMARY KEY,
> ret_name varchar(120) NOT NULL,
> ret_address1 varchar(120),
> ret_address2 varchar(120),
> sub_id integer NOT NULL CHECK
>                            ( EXISTS
>                            ( SELECT sub_id FROM suburbs )  
>                            )
> );
> 
> I guess that say's it all - no sub select's in Check or am I on the 
> wrong track ?

Postgresql 7.3 documentation:
"SQL Commands/CREATE TABLE":
...Currently, CHECK expressions cannot contain subselects nor refer to 
variables other than columns of the current row...

Regards,
Tomasz Myrta