On Thu, 20 Jun 2013 10:42:37 +0300, Technical Doubts
<online.technicaldoubts@xxxxxxxxx> wrote:
Team,
Am using Postgres 9.2
I am having a table
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?
You are not doing anything wrong. From documentation:
In general, a unique constraint is violated when there is more than one
row in the table where the values of all of the columns > included in
the constraint are equal. However, two null values are not considered
equal in this comparison. That means even in the presence of a unique
constraint it is possible to store duplicate rows that contain a null
value in at least one of the constrained columns. This behavior conforms
to the SQL standard, but we have heard that other SQL databases might
not follow this rule. So be careful when developing applications that
are intended to be portable.
Try not null constraints or functional unique indexes.
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
