pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Composite Unique Key - Doubt
3+ messages / 3 participants
[nested] [flat]

* Composite Unique Key - Doubt
@ 2013-06-20 07:42  Technical Doubts <online.technicaldoubts@gmail.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Technical Doubts @ 2013-06-20 07:42 UTC (permalink / raw)
  To: pgsql-admin

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?

Thanks in advance.

--
John.

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Composite Unique Key - Doubt
@ 2013-06-20 08:10  Emre Hasegeli <emre.hasegeli@tart.com.tr>
  parent: Technical Doubts <online.technicaldoubts@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Emre Hasegeli @ 2013-06-20 08:10 UTC (permalink / raw)
  To: pgsql-admin@postgresql.org, "Technical Doubts" <online.technicaldoubts@gmail.com>

On Thu, 20 Jun 2013 10:42:37 +0300, Technical Doubts  
<online.technicaldoubts@gmail.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.


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Composite Unique Key - Doubt
@ 2013-06-20 08:12  Thomas Kellerer <spam_eater@gmx.net>
  parent: Technical Doubts <online.technicaldoubts@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Thomas Kellerer @ 2013-06-20 08:12 UTC (permalink / raw)
  To: pgsql-admin

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2013-06-20 08:12 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-06-20 07:42 Composite Unique Key - Doubt Technical Doubts <online.technicaldoubts@gmail.com>
2013-06-20 08:10 ` Emre Hasegeli <emre.hasegeli@tart.com.tr>
2013-06-20 08:12 ` Thomas Kellerer <spam_eater@gmx.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox