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(at)tcinet(dot)net>
To: <pgsql-general(at)postgresql(dot)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(at)tcinet(dot)net
>