Re: some problems

Yes, you cannot place subqueries into check constraints
at this time.  It's probably possible to fix the immediate
problem (the unknown type stuff), but that doesn't actually
make the constraints work the way the SQL spec requires*,
so even if it gets fixed I'd suggest not using it until it
works completely.  I'd suggest doing a before insert/update
trigger instead.

* - Constraints must always be satisfied, and so a constraint
 with a subquery is actually constraining all tables mentioned
 as well as the one that the constraint is named on.  So,
 in your case, you would be unable to delete a row in id
 such that the constraint doesn't hold.  Fortunately
 constraints with subqueries appears to be a Full SQL
 feature.


Stephan Szabo
sszabo@bigpanda.com

On Wed, 23 Aug 2000, Roodie wrote:

> Ahoy!
>
> Hi!
> I have the following table:
>
> create table address
> (
>         id              int4 primary key,
>         owner           int4 not null,
>         content         varchar(80) not null
>         CHECK (owner in (select id from id where t_name in ('org',
> 'person',
> 'router', 'dns')))
> );
> create trigger delete_id before delete
>     on address for each row execute procedure delete_id();
> create trigger set_id before insert
>     on address for each row execute procedure set_id();
>
> Everything seems OK, but when I type this:
>
> ripv=# insert into address values(0, 18, 'Szeged');
>
> I get an error:
>
> ERROR:  ExecEvalExpr: unknown expression type 108
>
> But these queries work:
>
> ripv=# select 18 in (select id from id where t_name in ('org', 'person',
> 'router', 'dns'));
>  ?column?
> ----------
>  t
> (1 row)
>
> ripv=# select id from id where t_name in ('org', 'person', 'router',
> 'dns');
>  id
> ----
>   5
>  18
> (2 rows)
>
> ripv=#
>
> Any comment?
>
>
> --
> Roodie                    ICQ: 53623985
>  Linux, C++, VB, SQL, PhotoShop, Lightwave
>  Ars Magica,  AD&D,  Mutant Chronicles
>
>