Re: INDEX and NULL

"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