Re: INDEX and NULL

"Matthew Rudolph" <mrudolph(at)zetec(dot)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