From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-admin@postgresql.org
Subject: Re: Composite Unique Key - Doubt
Date: Thu, 20 Jun 2013 10:12:47 +0200
Message-ID: <kpudhi$ko2$1@ger.gmane.org> (raw)
In-Reply-To: <CAJyuQsHW0paQSWuy0GhzotvnrVxJf0vZNBWPqeZ8xGz8jo+Q7w@mail.gmail.com>
References: <CAJyuQsHW0paQSWuy0GhzotvnrVxJf0vZNBWPqeZ8xGz8jo+Q7w@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: spam_eater@gmx.net
Subject: Re: Composite Unique Key - Doubt
In-Reply-To: <kpudhi$ko2$1@ger.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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