> The UNIQUE constraint doesn't work on a field if I use a DEFAULT
> clause on a table.
> The following table works with UNIQUE constraint:
> but this one accept several same tcnom value:
> create table cltclt001(
> tcid int2 default nextval('cltcls001'),
> tcnom text unique
> );
> What's wrong with my table ?
Nothing. You have stumbled across a bug recently discovered by Mark
Dalphin <mdalphin@amgen.com> in the parser. It was repaired in the
source trees 1999-08-15 so will appear in v6.5.2 (any day now) and
v6.6.
postgres=> create sequence cltcls001;
CREATE
postgres=> insert into cltclt001 (tcnom) values ('one');
INSERT 150559 1
postgres=> insert into cltclt001 (tcnom) values ('one');
ERROR: Cannot insert a duplicate key into a unique index
I imagine that the repair is posted to the patches or hacker's mailing
list; look in the archives around that date and you should be able to
patch your existing recent system.
Good luck.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California