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
>
>
