Re: Unique index with Null value in one field

Hrishi Joshi wrote:

>Hi,
>
>I need to define a Unique index on 3 non-PK fields (composite key) on my
>table in PostgreSQL 8.0.3.
>
>The problem is, if any of those 3 fields is Null, PostgreSQL allows
>duplicate rows to be inserted. While searching through archives, I found
>more information about this.
>
>But I need to know how can I make PostgreSQL throw error on attempt to
>insert second record having same 3 field values, one of them being Null.
>
>
>------------------------------------------------
>myid | field1 | field2 | field3 | description
>PK | <--- Unique Index ---> |
>------------------------------------------------
>100 | ABC | XYZ | <null> | Record 1 -> This is ok.
>101 | ABC | XYZ | <null> | Record 2 -> * This should error!
>
>
Why? PostgreSQL knows that we cannot determine whether these records
are different or not. See the discussion on what NULL means...

IMO, people who use NULL to mean "not applicable" are misusing the
value. Not applicable should always be equal to not applicable, but it
is always unknown whether unknown is equal to another unknown. I would
suggest using a different table for that column if it is not always
applicable. The typical example is:

You have 500 employees. Some employees have salaries, some have wages.
Some people might:

create table employee(
employee_id serial,
ssn varchar not null,
...
wage numeric,
salary numeric
);

but here NULL could either mean "unknown" or "not applicable" so we
don't really know which is which and it can create data management issues.

In this case it is better to:

create table employee(
employee_id serial,
ssn varchar not null,
...
);
create table wage (
employee_id,
wage numeric
);
create table salary (
employee_id,
salary numeric
);

>------------------------------------------------
>
>Fields {field1, field2, field3} have unique index on them and "myid" is
>the primary key of my table.
>
>
>
>Oracle 9i throws exception in such case, but PostgreSQL does not.
>
>
You could use a custom trigger, or a custom function and a functional
index.... Or even an index on COALESCE...

But Oracle's handling of NULL's is broken, especially in string fields
(varchar, char, etc) because it wrongly assumes that an empty string and
NULL are equivalent. Search for prior discussions on this list....

Best Wishes,
Chris Travers
Metatron Technology Consulting