Re: diff. between Primary-Key and unique index

NULL is never stored in an index (be it primary or "just" unique)

Consider (making this work for you):

With primary keys, every record must have a non-null key, in the primary key index.
If one adds a record to a table, it MUST have a non-null key.

With unique (or non-unique) indexes, one may add a record to a table with a null in the indexed column, however, the record's pointer will not appear
in the index. One can take advantage of this when selecting records by using the INDEX hint, forcing selects to ignore those records with null

So, rather than saying

select * from my_table where indexed_field is not null

one can use

select /*+ INDEX( table_alias my_index) */ from my_table table_alias

Jan-Erik Rosinowski <spamfilter_at_rosinowski.de> wrote in message news:3889813e.873756_at_News.CIS.DFN.DE...
> >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
>
> 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..
>
> 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)
>
> anybody any further insights?
>
> ciao, jan
>
> http://www.rsp.de - Software zur effizienten Erstellung und Verwaltung von
> Gutachten, Analysen, Pruefprotokollen etc.
Received on Mon Jan 24 2000 - 16:53:02 CST