Thread: feature request: create table with unique constraint

begin ;

create table a (
  id serial primary key
  , info text not null -- not interested here
) ;

create table b (
  id serial primary key
  , a_id int not null references a
  , info text not null -- not interested here
  , actual bool not null
) ;

create unique index b_actual on b
  ( a_id , ( case when actual then 0 else id end ) )
;

create table c (
  id serial primary key
  , a_id int not null references a
  , info text not null -- not interested here
  , actual bool not null
  , unique ( a_id , ( case when actual then 0 else id end ) )
) ;


Why can i not define the unique constraint in the
create table? I know this is described in the manual but
why this is.



thanks
Andreas


btw Why must i give the index a name. The name should
be created automatically if none is specified as i
define a unique constraint in the create table.

<wumpus@z1p.biz> writes:
>   , unique ( a_id , ( case when actual then 0 else id end ) )

> Why can i not define the unique constraint in the
> create table?

The syntax for a unique constraint in CREATE TABLE is defined by the SQL
standard, and it doesn't include expressions.  More than the syntax, a
unique constraint also ought to show up in the information_schema views,
and those don't have the ability to handle expressions.  So we have the
capability, but it's only available via CREATE INDEX which is outside
the standard.

            regards, tom lane