Thread: Sub Select inside Check ?

Sub Select inside Check ?

From

Rudi Starcevic

Date:

25 February 2003, 07:43:50

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 ?

Thank you kindly
Regards
Rudi.

Re: Sub Select inside Check ?

From

Tomasz Myrta

Date:

25 February 2003, 10:42:28

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

Re: Sub Select inside Check ?

From

Stephan Szabo

Date:

25 February 2003, 14:05:10

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.