Re: NULL values seem to short-circuit my unique index

On Sep 29, 2008, at 4:59 AM, Chris wrote:

> Matthew Wilson wrote:
>> I'm trying to comprehend how NULL values interact with unique
>> indexes.
>> It seems like I can insert two rows with NULL values in a column
>> with a
>> unique constraint just fine.
>> Is there something special about NULL? Can anyone post some links to
>> explain what is going on?
>
> When you think of null as "unknown", it makes sense.
>
> Does an unknown value equal another unknown value?

Also, you wouldn't be able to put a UNIQUE constraint on foreign keys
with a 0..1 to 1 relation if two NULL values would be considered not
unique. That UNIQUE constraint is what makes it a 0..1 to 1 relation
(as would a PRIMARY KEY constraint). Without it it would be a * to 1
relation.

If two NULLs would be considered not unique, only one NULL key
reference would be allowed and all following ones would result in a
unique constraint violation!

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,49157dd89507271520953!