Re: INDEX and NULL

#1

"Matthew Rudolph" <mrudolph (AT) zetec (DOT) com> writes:

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: INDEX and NULL

comp.databases.postgresql.novice comp.databases.postgresql.novice

Re: INDEX and NULL - 12-22-2003 , 04:27 PM