The concepts guide goes on to say:
Unless a NOT NULL constraint is also defined, a null always satisfies a unique key constraint. Thus, columns with both unique key constraints and NOT NULL constraints are typical. This combination forces the user to enter values in the unique key and eliminates the possibility that new row data conflicts with existing row data.
Note:
Because of the search mechanism for unique key constraints on multiple columns, you cannot have identical values in the non-null columns of a partially null composite unique key constraint.A null is never equal (nor not equal) to any other null, hence, a unique constraint on a single attribute will allow as many null rows as you can stuff in there.
If you have a composite key (more than one attribute) then the non-null attributes must be unique. for example:
ops$tkyte%ORA11GR2> create table t ( x int, y int, constraint xy_unique unique(x,y) );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t values ( null, null );
1 row created.
ops$tkyte%ORA11GR2> insert into t values ( null, null );
1 row created.
<b>that works because we don't know if the rows are unique or not.</b>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t values ( 1, null );
1 row created.
ops$tkyte%ORA11GR2> insert into t values ( 2, null );
1 row created.
<b>those are unique because we know X is unique and we don't know about Y</b>
ops$tkyte%ORA11GR2> insert into t values ( 2, null );
insert into t values ( 2, null )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.XY_UNIQUE) violated
<b>that we know is not unique</b>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t values ( 1, 1 );
1 row created.
ops$tkyte%ORA11GR2> insert into t values ( 1, 2 );
1 row created.
<b>just like that</b>
a constraint may evaluate to TRUE, FALSE or *unknown* and they only fail when they evaluate to FALSE.
a primary key has semantic meaning, it is to be immutable (never changing in value), unique and non-null.
a unique constraint simply says "at any point in time, these values are unique - they may change and they may be null"
You use a unique constraint when
a) you don't already have a primary key since a table may only have one
b) you allow nulls in the attributes
c) you allow updates to the values in the attributes