Thread: INDEX and NULL

Here are the sample tables for my question.

CREATE TABLE bar (
    id        int2 PRIMARY KEY UNIQUE DEFAULT nextval('bar_id_seq'),
    ...
);

CREATE TABLE foo (
    id        int2 PRIMARY KEY UNIQUE DEFAULT nextval('foo_id_seq'),
    number    int2 NOT NULL,
    bar_id    int2 REFERENCES bar ON UPDATE CASCADE ON DELETE CASCADE,
    ...
);
CREATE UNIQUE INDEX foo_number_id_index ON foo(number, bar_id);

I am a novice for sure. I am trying to prevent multiple combinations
of the number and bar_id fields. However, since the bar_id can be NULL
I am actually getting multiple combinations with NULL.

For example,
foo:
id     number    bar_id .....
-------------------------------
1    |    1    |
2    |    2    |   3
3    |    1    |
....

Row 1 and 3 are duplicates that I wish to disallow. I am however seeing
just this behavior with 7.3.2. I am seeing the duplicates. I am not able
to add another row with number = 2 and bar_id = 3.

What am I doing wrong? What sort of NULLism is biting me?

I have searched around the lists and the documentation, I am sure it is
explained but I am not able to see the solution. I link that helps out
would be appreciated.

Thanks for your help.

Matthew
-----------------------------------------------------------------------
DISCLAIMER:  Information contained in this message and/or
attachment(s) may contain confidential information of Zetec, Inc.
If you have received this transmission in error, please notify
the sender by return email.
-----------------------------------------------------------------------

"Matthew Rudolph" <mrudolph@zetec.com> writes:
> CREATE UNIQUE INDEX foo_number_id_index ON foo(number, bar_id);

> I am a novice for sure. I am trying to prevent multiple combinations
> of the number and bar_id fields. However, since the bar_id can be NULL
> I am actually getting multiple combinations with NULL.

> For example,
> foo:
> id     number    bar_id .....
> -------------------------------
> 1    |    1    |
> 2    |    2    |   3
> 3    |    1    |
> ....

> Row 1 and 3 are duplicates that I wish to disallow.

You're more or less out of luck on this, because that is not the
behavior that SQL specifies for NULLs.  (If you want a rationalization
for this, consider that NULL behaves like "unknown".  Rows 1 and 3
cannot be said to be duplicates: rather, it's unknown whether they are
duplicates, because we don't know what the two values of bar_id are.
The UNIQUE constraint is defined to allow this situation.)

You might be best off to use some specific non-null dummy value (perhaps
zero or -1?) for empty bar_id entries, and constrain the column to be
NOT NULL.  Then the UNIQUE constraint would act the way you want.

            regards, tom lane