Обсуждение: Composite Unique Key - Doubt

On Jun 20, 2013, at 1:56 AM, Technical Doubts <online.technicaldoubts@gmail.com> wrote:

> 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?

Reason is null is not equal to null.

So, 123,'P',null and 124,'P',null is actually unique composite key.

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Postgres Database Company
Blog:http://vibhork.blogspot.com

On Jun 20, 2013, at 12:08, Vibhor Kumar <vibhor.kumar@enterprisedb.com> wrote:

>
> On Jun 20, 2013, at 1:56 AM, Technical Doubts <online.technicaldoubts@gmail.com> wrote:
>
>> …
>> technologies
>> (
>> technologyid bigint,
>> status character(1),
>> implementeddate date
>> *CONSTRAINT technologies_uq UNIQUE (technologyid, status, implementeddate)*
>> )
>> …
>> insert into technologies (technologyid,status,implementeddate)
>> values
>> (123,'P',null),
>> (123,'P',null);
>> …
>> table accepting duplicate values in spite of composite unique constraint..
>> where I am doing wrong?
>
> Reason is null is not equal to null.
>
> So, 123,'P',null and 124,'P',null is actually unique composite key.

This is one example of why many folks including Dr. Chris Date recommend against using NULL in SQL.

As a workaround, you could add two more constraints:
• Make 'implementeddate' NOT NULL
• Assign to 'implementeddate' an arbitrary date as a DEFAULT value for new records.

With those two additional constraints in place, your composite unique constraint would then work if your intention is
thatat most only one combination of 'technologyid' + 'status' should ever exist without an actual date assigned. 

Personally, I would choose '1970-01-01' (Unix Epoch) as the arbitrary value with the implicit meaning of 'no date yet
assigned'.But you could choose any date that would never be otherwise assigned as an actual date. 

https://en.wikipedia.org/wiki/Null_(SQL)#Criticisms
http://www.w3schools.com/sql/sql_notnull.asp
http://www.w3schools.com/sql/sql_default.asp
http://en.wikipedia.org/wiki/Unix_time

--Basil Bourque