A m Z o n e

Primary key with non-unique index

Can a primary key exist with non-unique index?
When a primary key is created on a table, Oracle will create a unique index on the mentioned columns. Is it possible for a Primary key to have non-unique index?

Yes it can.
create a non-unique index on a column and then mark it as primary key. Oracle will continue using the existing index for enforcing primary key constraint! Please note that Oracle will still prevent duplicate values from entering the table.

As per my testing, I have not come across any issues if such a scenario exists. I cannot trace if any overhead is encountered. Bottom line, don't panic if primary key is found with a non-unique index. For DBAs, take care of this scenario when creating related scripts.

Press the Back button of you Browser to go to previous page
Home