From: | "Emre Hasegeli" <emre(dot)hasegeli(at)tart(dot)com(dot)tr> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org, "Technical Doubts" <online(dot)technicaldoubts(at)gmail(dot)com> |
Subject: | Re: Composite Unique Key - Doubt |
Date: | 2013-06-20 08:10:11 |
Message-ID: | op.wyywy9hik2xoe5@hasegeli.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Thu, 20 Jun 2013 10:42:37 +0300, Technical Doubts
<online(dot)technicaldoubts(at)gmail(dot)com> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2013-06-20 08:12:47 | Re: Composite Unique Key - Doubt |
Previous Message | Technical Doubts | 2013-06-20 07:42:37 | Composite Unique Key - Doubt |