Oracle Unique Indexes

Question

I was creating a new table today in 10g when I noticed an interesting behavior. Here is an example of what I did:

CREATE TABLE test_table ( field_1 INTEGER PRIMARY KEY );

Oracle will by default, create a non-null unique index for the primary key. I double checked this. After a quick check, I find a unique index name SYS_C0065645. Everything is working as expected so far. Now I did this:

CREATE TABLE test_table ( field_1 INTEGER,
CONSTRAINT pk_test_table PRIMARY KEY (field_1) USING INDEX (CREATE INDEX idx_test_table_00 ON test_table (field_1)));

After describing my newly created index idx_test_table_00, I see that it is non-unique. I tried to insert duplicate data into the table and was stopped by the primary key constraint, proving that the functionality has not been affected. It seems strange to me that Oracle would allow a non-unique index to be used for a primary key constraint. Why is this allowed?

Answer 1

There is actually no structural difference between a unique index and a non-unique index, Oracle can use either for the PK constraint. One advantage of allowing a PK definition like this is that you can disable or defer the constraint for data loading - this isn't possible with a unique index, so one could argue that this implementation is more flexible.

Answer 2

Why not allow it? I love that Oracle gives you lots of options and flexibility.

Maybe you can create one index and use it for two purposes:

• validate the PK
• help a query perform better

Oracle will by default create a non-null unique index

Oh, and the index has nothing to do with the not null aspect.

Answer 3

Hi Melvin,

see this excellent article about non-unique indexes policing primary keys by Richard Foote. Richard shows that you will take a performance hit when using a non-unique index.

In other words: don't use non-unique indexes to police a primary key constraint unless you really need the constraint to be deferrable.