For those whose interest and/or ire may have been sparked by the recent unique
constraint discussion between John and I regarding the data compare/sync using
unique keys and what unique keys actually do (or do not do) – I thought
I’d copy some Oracle docs on the topic for those who may not have liked
the answer about unique keys.
The key sentence in the entire section for unique keys is this: A null in a
column (or in all columns of a composite UNIQUE key) always satisfies a UNIQUE
Note too that foreign keys follow a similar unobvious rule: If any column of a
composite foreign key is null, then the non-null portions of the key do not have
to match any corresponding portion of a parent key.
I’ve been teaching Oracle since the early 90’s and always made sure
to show people this section from the concepts guide – because the way
constraints work is not always what people expect. So this material is handy to
remember when defining keys. Note that there are simply pictures that clearly
relate this behavior (see big arrows below) J
UNIQUE Key Integrity Constraints
A UNIQUE key integrity constraint requires that every value in a column or set
of columns (key) be unique—that is, no two rows of a table have duplicate
values in a specified column or set of columns.
For example, in Figure 21-3 a UNIQUE key constraint is defined on the DNAME
column of the dept table to disallow rows with duplicate department names.
Figure 21-3 A UNIQUE Key Constraint
Description of “Figure 21-3 A UNIQUE Key Constraint”
The columns included in the definition of the UNIQUE key constraint are called
the unique key . Unique key is often incorrectly used as a synonym for the terms
UNIQUE key constraint or UNIQUE index . However, note that key refers only to
the column or set of columns used in the definition of the integrity constraint.
If the UNIQUE key consists of more than one column, then that group of columns
is said to be a composite unique key . For example, in Figure 21-4 the customer
table has a UNIQUE key constraint defined on the composite unique key: the area
and phone columns.
Figure 21-4 A Composite UNIQUE Key Constraint