Re: Composite Unique Key - Doubt

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.