Oracle : Primary Keys and Non-Unique Indexes

I was asked by a colleague recently about "Unique and Nonunique Indexes" in Oracle Concepts guide :

Oracle recommends that unique indexes be created explicitly, using CREATE UNIQUE INDEX. Creating unique indexes through a primary key or unique constraint is not guaranteed to create a new index, and the index they create is not guaranteed to be a unique index.

Well, I was as puzzled as she was, by the following two facts:

1. Creating unique indexes through a primary key or unique constraint is not guaranteed to create a new index.
2. The index they create is not guaranteed to be a unique index.

Looking at the "PRIMARY KEY Integrity Constraints" of the same guide, it says :

Oracle Database enforces all PRIMARY KEY constraints using indexes. The primary key constraint created for a column is enforced by the implicit creation of:

• A unique index on that column
• A NOT NULL constraint for that column

Composite primary key constraints are limited to 32 columns, which is the same limitation imposed on composite indexes. The name of the index is the same as the name of the constraint. Also, you can specify the storage options for the index by including the ENABLE clause in the CREATE TABLE or ALTER TABLE statement used to create the constraint. If a usable index exists when a primary key constraint is created, then the primary key constraint uses that index rather than implicitly creating a new one.

The important part is "If a usable index exists when a primary key constraint is created, then the primary key constraint uses that index rather than implicitly creating a new one". I was under the false impression that set a PRIMARY KEY constraint on a table would create a unique index. Well, no, not necessarily. Oracle will reuse (thus not even create) a non-unique index for the constraint, when the columns on which the index is are the same as those for the primary key. This is also true for indexes having more columns than the primary key. As long as the columns used for the primary are the same as the first columns used for the index, the index will be reused. Unique or not.

Another thing to know is "Deferred Constraint Checking". From the Concepts guide :

You can defer checking constraints for validity until the end of the transaction.

• A constraint is deferred if the system checks that it is satisfied only on commit. If a deferred constraint is violated, then commit causes the transaction to undo.
• If a constraint is immediate (not deferred), then it is checked at the end of each statement. If it is violated, the statement is rolled back immediately.

What happens when a PRIMARY KEY is created as being DEFERRABLE ? Until the transaction is committed, it is allowed to have duplicate values. So a unique index cannot be used. Oracle has to use a non-unique index. Again, a primary key constraint is not necessarily ensured by a unique index.

The fun part is that :

• If a unique index was used by a primary key constraint and that constraint is dropped, the index will also be dropped !
• If a non-unique index was used by a primary key constraint and that constraint is dropped, the index will not be dropped.

References

• Primary Keys and Non-Unique Indexes (What's Really Happening ?) by Richard Foote
• What is the difference between a unique index and a unique constraint?
• Oracle documentation