To: pgsql-admin@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: Composite Unique Key - Doubt
Date: Thu, 20 Jun 2013 10:12:47 +0200
Lines: 45
Message-ID: <kpudhi$ko2$1@ger.gmane.org>
References: 
 <CAJyuQsHW0paQSWuy0GhzotvnrVxJf0vZNBWPqeZ8xGz8jo+Q7w@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de;
 rv:1.8.1.21) Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666
In-Reply-To: 
 <CAJyuQsHW0paQSWuy0GhzotvnrVxJf0vZNBWPqeZ8xGz8jo+Q7w@mail.gmail.com>
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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