I admit it, I had a completely wrong belief when thinking about primary key constraints and their related index. When creating a PK on a table, Oracle automatically associates a unique index to it

The index name is internally generated and associated to the constraint. Fact is that, as I discovered some time ago, it’s not the index that enforces the uniqueness but the constraint itself. The constraint uses the underlying index to go faster but the index itself does not enforce anything. But you can’t really blame me for thinking differently in the beginning, not after seeing that Oracle creates a unique index for the pk!
As a proof of this we can create a deferrable primary key and take a look at the index that Oracle creates

As we can notice, the index is not unique and this makes perfect sense otherwise we would not be able to temporarily insert duplicate values into this table (don’t forget that the constraint is deferrable).
We can also create our own index on the same PK column before creating the constraint. If the constraint is not deferrable the index can be either unique or non-unique otherwise it has to be non-unique. Oracle will use the existing index if those requirements are met

As shown in the INDEX_NAME of the USER_CONSTRAINTS view, Oracle took our index to speed up the constraint enforcement.
So what about using a custom unique index that is defined on a set of columns that is bigger than the one on which the PK is defined? Let’s try that and see what happens

In this case Oracle refused to use our index and created its own SYS_C0033773 for supporting the constraint. Also forcing our index does not work

We can still do something else: let’s try to use a non-unique index this time

Now it works and we don’t even need to specify the index name, Oracle picks our MYTAB_IDX because it satisfies the requirements! What about an index that is contains the same columns of our PK but not in the leading position? Let’s have a try

Nope, it doesn’t work (but I was kind of expecting that!) and neither forcing it works.
I want to show one last interesting thing. When we create a PK constraint Oracle automatically creates an index. If we drop that constraint the index is dropped as well. This does not happen when we create the index ourselves. So, what’s the difference between those indexes? There is the column GENERATED in the USER_INDEXES view that is set to Y for all indexes that have been created by Oracle to support a constraint and to N for all other user-created indexes. This also makes perfect sense because the index we create might be used to optimize our queries and we don’t want it to be dropped with the constraint!
I’ll end this post with the answer to one question that might be bouncing in your head: why should we ever use custom indexes for PK constraints if Oracle already takes care of them?
The answer is that you might have a very big table and creating such index could be a real problem. In that case you can create the index separately using parallel and nologging options in order to dramatically speed up the process.

Summing up:

  • custom UNIQUE indexes can be used only if columns match the constraint ones and this is not DEFERRABLE;
  • custom NON-UNIQUE indexes can be always used to enforce primary keys if the constraint columns are the leading ones of the index;
  • DEFERRABLE primary key constraints always need a NON-UNIQUE index (rule of leading columns applies here too).
Share this post on Share on Facebook
Tweet about this on Twitter
Share on LinkedIn
Email this to someone

Leave a Reply

Your email address will not be published. Required fields are marked *