Re: problem with check constraint using a select command.

At this time check constraints using subqueries is non-functional.

Even if the immediate problem of allowing the subquery in the check
constraint was fixed, the larger problem of the fact that a subquery
check constraint is actually also a constraint on the tables mentioned
in the subquery still remains.  (In your case, you could not delete a row
such that the select distinct no longer returned a value that was referenced
in the instance_object table.)  For now, we'll have to hide behind the
leveling rules and say that it's not yet implemented because it's not
allowed
in either entry or intermediate sql. ;)

As for doing what you want, your best bet is to define a before
insert/update
trigger on the table that does the check and exceptions if the value is
invalid.
That's not quite the constraint unless you added an equivalent on
update/delete
trigger to the other table to prevent the modifications.

----- Original Message -----
From: "George Henson" <george@tcinet.net>
To: <pgsql-general@postgresql.org>
Sent: Monday, July 24, 2000 12:03 PM
Subject: [GENERAL] problem with check constraint using a select command.


> I am running PostgreSQL v 7.0.2 on Linux 2.0.36
>
> I have a table that holds definitions of objects. I have a second table to
> hold the instances of these objects ( and the current values)
>
> I would like to insure the instances are valid master object types.
>
> I started using a check constraint on the columns but I got a
ExecEvalExpr:
> unknown expression type 108
>  error.
>
> My table definitions and data inserts are below.
>
> Thank you
>
> create table master_objects (
>   obj_type_html varchar(32),
>   obj_lang      varchar(2),
>   obj_type_lang varchar(50),
>   obj_comment   varchar(200),
>   constraint master_objects_pk primary key (obj_type_html, obj_lang)
> );
>
> create table instance_object (
>   object_id     char(32),
>   obj_type_html varchar(32),
>   obj_lang      varchar(2),
>   object_parent char(32),
>   display_order int,
>   constraint instance_object_pk primary key (object_id),
>   constraint instance_object_fk foreign key (object_parent)
>     references instance_object (object_id),
>   constraint instance_object_obj_type_html_ck check (obj_type_html in
>     (select distinct obj_type_html from master_objects)),
>   constraint instance_object_obj_lang_ck check (obj_lang in
>     (select distinct obj_lang from master_objects))
> );
>
> insert into master_objects
>   (obj_type_html, obj_lang, obj_type_lang, obj_comment)
>     values
>   ('image', 'EN', 'Image', 'This is an image')
> ;
>
> insert into instance_object
>   (object_id, object_parent)
>     values
>   ('image', 'EN')
> ;
>
>
>
> --
> George Henson
> george@tcinet.net
>