Re: URGENT: Primary key vs Unique Index

It is a cardinal rule of relational databases that every row in a table is uniquely identified by its primary key. Since a null value means one of (unknown, not applicable, unspecified + many other reasons for being "missing"), you can't assert that two rows that both have nulls in the column represent the same occurence.

In Oracle, null is not considered equal to null so, for instance select * from dual where null = null
will return no rows

However, if you define a unique key on columns a and b where b allows null and you attempt to create two rows with the same value of a and b null in both cases then a unique constraint violation occurs. This is the only time that null is considered to be equal to null.

Ian Stevenson
co-author of Oracle Design by O'Reilly and Associates

Mars <mars_at_mediadata.com.sg> wrote in article <33133DFD.6162_at_mediadata.com.sg>...
> Bill Bearden wrote:
> >
> > There are two important differences between Primary and Unique Keys.
> >
> > 1. There can only be one Primary Key on a table. There can be more
 than
> > one column with Unique Key.
> > 2. Primary Key columns can't contain NULL. Unique Key columns can.
> >
>
> Pardon my ignorance but why can't a primary key be NULL ? Is it a rule
> or is it specific to just Oracle ?
>
> rgds,
> mars
>
Received on Tue Feb 25 1997 - 00:00:00 CST