From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Rudi Starcevic <rudi(at)oasis(dot)net(dot)au> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Sub Select inside Check ? |
Date: | 2003-02-25 15:50:13 |
Message-ID: | 20030225074841.X57635-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Tue, 25 Feb 2003, Rudi Starcevic wrote:
> 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 ?
Unfortunately, that's not supported (it's non-trivial). If you're
looking only for an insert/update on cp_retailers check (ie, suburbs
is unlikely to be updated/deleted from), you can do this by hiding
the subselect in a function and using that in the check. If you
need both direction checks it gets rather complicated to do well, but
you can come fairly close using after triggers.
From | Date | Subject | |
---|---|---|---|
Next Message | Victor Yegorov | 2003-02-25 15:50:21 | Relation "pg_relcheck" |
Previous Message | Tom Lane | 2003-02-25 15:32:40 | Re: STORAGE and GiST |