Technical Doubts wrote on 20.06.2013 09:42:
technologies
(
technologyid bigint,
status character(1),
implementeddate date
*CONSTRAINT technologies_uq UNIQUE (technologyid, status, implementeddate)*
)
entering data as
insert into technologies (technologyid,status,implementeddate)
values
(123,'P',null),
(123,'P',null);
2 rows affected.
table accepting duplicate values in spite of composite unique constraint..
where I am doing wrong?
That's because of the null values. Any comparison with NULL yields "unknown" and in case of a
constraint this means the constraint is not violated.
Apparently you can not make that column NOT NULL (which would prevent this situation).
But you could create a unique index on an expression that treats NULL as "some value", e.g:
create table technologies
(
technologyid bigint,
status character(1),
implementeddate date
);
create unique index technologies_uq
on technologies (technologyid, status, coalesce(implementeddate, date '1900-01-01'));
A unique constraint is slightly different to a unique index (e.g. it cannot be the target
of a foreign key) but it would server your purpose in this case - unlesse you have
requirements you did not mention.
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
