Uniqueness in a Nullable Column

[wendy@cs.queensu.ca]

Just curious to know if there is a simple way to ensure uniqueness
among non-null values within a nullable column.

For instance if I have a column of Names that can contain null values,
I would like to ensure that all names that actually have a value are
unique.

Thanks!

Wendy

[ak_tiredofspam@yahoo.com]

Re: Uniqueness in a Nullable Column

from a theoretical point of view, you might have a design flaw. In some
cases it is better to have a separate table Names with a UNIQUE
constraint on it, and have your original table refer to it via an FK
constraint.

Having said that, you can add another column, let's say N, and set it
to -1 for all NOT NULL names, and populate it from a sequence starting
from 0 if the name is null. Create a UNIQUE index on (Name, N) and you
are all set

[Mark Yudkin]

Re: Uniqueness in a Nullable Column

Actually, from a theoretical viewpoint "a null is value distinct from any
other value INCLUDING NULL" (see Codd, accent mine). Even DB2 will return
unknown, not true for NULL = NULL.

Unfortunately, DB2 UDB's unique index fails to implement the semantic,
leading to the need for workarounds such as yours.

<ak_tiredofspam @yahoo.com> wrote in message
news:1105379487 .134694.188910@ c13g2000cwb.goo glegroups.com.. .[color=blue]
> from a theoretical point of view, you might have a design flaw. In some
> cases it is better to have a separate table Names with a UNIQUE
> constraint on it, and have your original table refer to it via an FK
> constraint.
>
> Having said that, you can add another column, let's say N, and set it
> to -1 for all NOT NULL names, and populate it from a sequence starting
> from 0 if the name is null. Create a UNIQUE index on (Name, N) and you
> are all set
>[/color]

[efiryago@gmail.com]

Re: Uniqueness in a Nullable Column

theoretically, you could create a statement level trigger that checks
for duplicates amongst all not null values in this column.

[ak_tiredofspam@yahoo.com]

Re: Uniqueness in a Nullable Column

>> Actually, from a theoretical viewpoint "a null is value distinct
from any
other value INCLUDING NULL" (see Codd, accent mine). Even DB2 will
return unknown, not true for NULL = NULL
.... <<

note that DB2 (as well as Oracle) will also return unknown for NULL <>
NULL. In this particular contest "distinct from any other value " means
"not(equal to any other value), unknown accepted", and this is exactly
what's implemented by Oracle
[color=blue][color=green]
>> Unfortunately, DB2 UDB's unique index fails to implement the[/color][/color]
semantic,
leading to the need for workarounds such as yours.
.... <<

DB2 UDB implements a stronger requirement
"distinct from any other value " meaning "not equal to any other value,
unknown NOT accepted".