Thread: CONSTRAINT problems

I've got a test table in 7.0.3 where I'm implementing a directory
type structure in a RCS type system. I have a check to ensure that
there are no duplicate filenames within for a specific directory
given the revisionid of 0 (means it's the current revision rather
than a historical one).

CREATE TABLE test (
  id int4 primary key,
  name VARCHAR(20),
  revision int4 default 0,
  directoryid int4,
  CONSTRAINT dupename CHECK (NOT EXISTS (SELECT name FROM test as o
WHERE o.name!=name AND revision=0))
);
CREATE
insert into test values (1,'test','0',NULL);
ERROR:  ExecEvalExpr: unknown expression type 108

-Michael
_________________________________________________________________
     http://fastmail.ca/ - Fast Free Web Email for Canadians

This a known problem which is caused by subselects in check constraints.
Current sources seem to give an error message at create time:
    ERROR:  Cannot use subselect in CHECK clause

In general subselects in check constraints also may constrain tables
that the subselect mentions.  In your case this wouldn't probably be
a problem, but it's difficult to tell the cases apart.

You can probably do this with a before insert/update trigger in
pl/pgsql at the current time though.

> I've got a test table in 7.0.3 where I'm implementing a directory
> type structure in a RCS type system. I have a check to ensure that
> there are no duplicate filenames within for a specific directory
> given the revisionid of 0 (means it's the current revision rather
> than a historical one).
>
> CREATE TABLE test (
>   id int4 primary key,
>   name VARCHAR(20),
>   revision int4 default 0,
>   directoryid int4,
>   CONSTRAINT dupename CHECK (NOT EXISTS (SELECT name FROM test as o
> WHERE o.name!=name AND revision=0))
> );
> CREATE
> insert into test values (1,'test','0',NULL);
> ERROR:  ExecEvalExpr: unknown expression type 108