Unique Oracle Indexes

Question

I created 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 create a nonzero unique index for the primary key by default. I double checked this. After a quick check, I find a unique index name SYS_C0065645. So far, everything is working as expected. Now I have done 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 idx_test_table_00 index, I see that it is not unique. I tried to insert duplicate data into the table and was stopped by restricting the primary key, proving that the functionality was not affected. It seems strange to me that Oracle can use a non-ideal index to restrict the primary key. Why is this allowed?

Answer 1

There is really no structural difference between a unique index and a non-true index, Oracle can use either to restrict PK. One of the advantages of providing such a PK definition is that you can disable or postpone the restriction on loading data - this is not possible with a unique index, so it can be argued that this implementation is more flexible.

Answer 2

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

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

• check PK
• Help fulfill the request better

Oracle will create a nonzero unique index by default

, .

Answer 3

see this great article on unique indexes that control Richard Foot's primary keys . Richard shows that you will use the performance hit when using a unique index.

In other words: do not use different indexes to protect the primary key constraint if you really need the constraint to be deferred.