Re: diff. between Primary-Key and unique index

Hi,

In article <3889813e.873756_at_News.CIS.DFN.DE>,   spamfilter_at_rosinowski.de wrote:
> >if a column is declared as a unique key it will allow null
> >value in that column (but only once) since the column is unique and
> >therefore null values can occur but only once
>

Actually, this is not 100 percent correct (al least for Oracle) Oracle allows multiple rows with NULL in a unique index (or constraint for that matter), since NULL entries are not indexed. This applies only for single-column indexes, composite indexes behave different: If one value is not null, the row will be indexed. This implies, that you can only have one ('Value', NULL) tupel as opposed to any number of (NULL) tupels.
Not sure, whether this complies with ANSI standards. Sometimes, this inconsistent behavior causes problems, e.g. if your model evolves, and you want to add another column to your unique index. Suddenly, you cannot have multiple NULL values in the original column.

> from my point of understanding 'null' is similar to infinity in
> mathematics. nothing can be equal to infty. for that very reason null
> IS null but (null=null) IS null..

correct.

>
> so i don't understand how null can be really unique or why on the
> other hand, null isn't allowed in a pk (w/ the same semantics)
>

The idea of a pkey is to have a unique handle to each row of data. Null semantics would break this paradigm since NULL is never equal to anything.

Karsten

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Jan 22 2000 - 07:08:25 CST