Why Does Oracle Sometimes Not Drop an Index Associated with a Primary Key or Unique Constraint?

Database schema migration is never an easy job. In fact, it can really be a headache, even when you’re working with a familiar system. For example, at times Oracle 10g may not drop the associated index for a primary key or unique constraint that has been dropped. In this article, I am going to explain when and why this happens.

The Story:

I’ve been working on the development of an e-commerce platform. Before every new platform version release, a migration script is prepared to move the database scheme and data from the old version to the new one. This is done by the database architect in his local environment. Then the migration script is tried out against a test environment that restores the production database and simulates real conditions. (Restoring the production database in the test environment is done using the Oracle 11g Data Pump tool.)

Some time ago, our team came across a problem while testing the migration script; the problem was not present on the local environment. It turned out that the architect wrote the migration script in such a manner that it expected to drop the associated index when the primary key or unique constraint was dropped. This assumption worked locally, but not on the testing environment.

I investigated the issue and found out that Oracle does not always drop an associated index while dropping the primary key or unique constraint. It depends when the index was created in relation to the constraint. Let’s examine this further.

Note: The following explanations will focus on primary key constraint creation, but unique constraint behavior in Oracle is similar.

Creating a Primary Key Constraint on a Non-Indexed Column

In Oracle, every primary key or unique constraint within a table exists with an associated index.

To demonstrate this, let’s create a table and check whether it has any associated constraints or associated indexes.

SQL> create table "some_user" (
  2     id number(10,0),
  3     first_name varchar2(40),
  4     last_name varchar2(40)
  5  );

Table created.

SQL> select constraint_name, 
  2         constraint_type, 
  3         table_name,
  3         index_name 
  4  from user_constraints 
  5  where table_name='some_user';

no rows selected

SQL> select index_name, 
  2         table_name 
  3  from user_indexes 
  4  where table_name = 'some_user';

no rows selected

As we can see, it does not. Not quite unexpected, is it? So let’s add a primary key constraint and see if anything interesting happens.

SQL> alter table "some_user" add constraint user_pk primary key (id);

Table altered.

Now, let’s check for the constraint and index.

SQL> select constraint_name,
  2         constraint_type,
  3         table_name,
  4         index_name
  5  from user_constraints
  6  where table_name='some_user';

CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME INDEX_NAME
--------------- --------------- ---------- ----------
USER_PK         P               some_user  USER_PK 

SQL> select index_name,
  2         table_name
  3  from user_indexes
  4  where table_name = 'some_user';

INDEX_NAME TABLE_NAME
---------- ----------
USER_PK    some_user

We can see that a primary key constraint was added, along with an associated index.

Automatically adding an index is one of the database features that have come along with the arrival of Oracle 10g. This applies to creating both a primary key constraint and a unique constraint. Moreover, Oracle automatically creates an index for LOB storage, XMLType, and materialized view. However, we’ll focus on primary key and unique constraints for simplicity’s sake.

Now, let’s observe what happens to the index associated with the primary key constraint if the primary key constraint is dropped.

SQL> alter table "some_user" drop constraint user_pk;

Table altered.

SQL> select index_name, 
  2         table_name 
  3  from user_indexes 
  4  where table_name = 'some_user';

no rows selected

What’s goin’ on? The index is gone! Why?!

The index was created for the purpose of the primary key constraint. It was automatically dropped by the same command that drops the primary key constraint.

Creating a Primary Key Constraint on an Indexed Column

What if an index already exists when the primary key constraint is created? Would the constraint creation add another index to the column? Let’s check it.

We will create another table, put an index on its id column, and then create a primary key constraint on the id column.

SQL> create table "some_user_2" (
  2    id number(10,0),
  3    first_name varchar2(40),
  4    last_name varchar2(40)
  5  );
	
Table created.

SQL> create index some_user_2_pk on "some_user_2"(id);

Index created.

SQL> alter table "some_user_2" add constraint user_pk primary key (id);

Table altered.

Have a look at which index is associated with the constraint.

SQL> select constraint_name,
  2         constraint_type,
  3         table_name,
  4         index_name
  5  from user_constraints
  6  where table_name='some_user_2';

CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME  INDEX_NAME
--------------- --------------- ----------- --------------
USER_PK         P               some_user_2 SOME_USER_2_PK

SQL> select index_name,
  2         table_name
  3  from user_indexes 
  4  where table_name = 'some_user_2';

INDEX_NAME     TABLE_NAME
-------------- -----------
SOME_USER_2_PK some_user_2

As we can see, the existing index was used for the primary key constraint creation; no new index was created. When Oracle creates a primary key constraint and can make use of any existing index for it, it does not create a new index. In particular, a multiple-column index may be used if its leading column is referred by the primary key constraint.

Now, let’s drop the primary key constraint and check what happens to the index.

SQL> alter table "some_user_2" drop constraint user_pk;

Table altered.

SQL> select index_name,
  2         table_name
  3  from user_indexes
  4  where table_name = 'some_user_2';

INDEX_NAME     TABLE_NAME
-------------- -----------
SOME_USER_2_PK some_user_2

The index is still there! Were we expecting that? To be honest, it’s perfectly reasonable not to drop the index while dropping the primary key constraint if the index was not created with the primary key constraint, isn’t it?

Remember:

Automatically adding an index when none can be reused has been a database feature since Oracle 10g. This applies to following situations:

• creating a primary key constraint,
• creating a unique constraint,
• creating LOB storage and XMLType,
• creating a materialized view

Dropping an associated index while simultaneously dropping the primary key constraint depends on whether the index existed before the constraint. If the index was created with the primary key constraint, then it is dropped when the constraint is dropped. Otherwise – if it was there before the constraint – it is not dropped at all.

This is what happened to our team’s database architect. The Oracle Data Pump Import tool (impdp) works by separately creating the index and then adding the primary key constraint. Since during the import both things happen separately, dropping the primary key constraint will NOT drop the index.

When we drop the constraint, rarely do we have the opportunity to know whether an associated index existed before the primary key constraint creation. Therefore you cannot rely on dropping the associated index along with the primary key constraint. You should keep that in mind while writing database scheme and data migration scripts!

Do you know any other Oracle migration pitfalls? Please reply in the comment section if you do.