Oracle Unique Indexes
- by Melvin
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?
