Boneist's Oracle Blog

In a recent release that I’ve been working on, we had to drop and recreate some of the unique/primary key constraints (due to columns being added into the constraint) on some of our tables.

Prior to working in this team, I had only ever come across explicitly creating the indexes for a table, similar to:

create table test_explicit_index (col1 number,
                                  col2 varchar2(30),
                                  col3 date);

create unique index col1_exp_pk on test_explicit_index (col1);

We can see the index exists:

select index_name, table_name, uniqueness, status
from   user_indexes where table_name = 'TEST_EXPLICIT_INDEX';

INDEX_NAME   TABLE_NAME           UNIQUENESS STATUS  
------------ -------------------- ---------- --------
COL1_EXP_PK  TEST_EXPLICIT_INDEX  UNIQUE     VALID   

If we drop the constraint, we can see that the index still exists:

alter table test_explicit_index drop constraint col1_exp_pk;

select index_name, table_name, uniqueness, status
from   user_indexes where table_name = 'TEST_EXPLICIT_INDEX';

INDEX_NAME   TABLE_NAME           UNIQUENESS STATUS  
------------ -------------------- ---------- --------
COL1_EXP_PK  TEST_EXPLICIT_INDEX  UNIQUE     VALID   

Therefore, in my release scripts, I would always run a drop index statement after dropping a constraint supported by an index.

All was well with the world, until I moved to a team who created constraints that implicitly created the index, something like:

create table test_implicit_index (col1 number,
                                  col2 varchar2(30),
                                  col3 date);

alter table test_implicit_index add constraint col1_imp_pk primary key (col1) using index;

Once again, we can see that the index exists:

select index_name, table_name, uniqueness, status
from   user_indexes where table_name = 'TEST_IMPLICIT_INDEX';

INDEX_NAME   TABLE_NAME           UNIQUENESS STATUS  
------------ -------------------- ---------- --------
COL1_IMP_PK  TEST_IMPLICIT_INDEX  UNIQUE     VALID 

But what happens when we drop the constraint?

alter table test_implicit_index drop constraint col1_imp_pk;

col index_name format a12;
col table_name format a20;

select index_name, table_name, uniqueness, status
from   user_indexes where table_name = 'TEST_IMPLICIT_INDEX';

no rows selected.

The index is automatically dropped!

Again, all was well in the world – I no longer needed to explicitly drop the index first… great!

That is, until I encountered “ORA-00955: name is already used by an existing object” when trying to recreate the constraint with the extra columns. After researching, I discovered that although the constraint wasn’t present, the index still was. Oh no! Inconsistently created objects!

It was at this point, I remembered that Neil Chandler had written a blog entry about how to tell if the index is going to be dropped when dropping the corresponding constraint. Turns out, there isn’t an easy way – you have to use the sys.ind$ table which, of course, I don’t have access to!

“Ah well,” I thought, “I’ll simply have to code a bit of PL/SQL to drop the index if it exists, something along the lines of:

begin
  execute immediate 'drop index col1_exp_pk';
exception
  when others then null;
end;
/

However, I wasn’t really happy with this solution; it seemed… awkward. After doing some additional research, it turns out that Oracle have catered for dropping the index at the same time as the constraint, as mentioned in the documentation!

So now, in my release scripts, instead of a clunky PL/SQL procedure, I can just encapsulate the index drop in one command:

create table test_explicit_index2 (col1 number,
                                   col2 varchar2(30),
                                   col3 date);

create unique index col1_exp2_pk on test_explicit_index2 (col1);

alter table test_explicit_index2 add constraint col1_exp2_pk primary key (col1);

-- **** Ensure the index is dropped along with the constraint: ****
alter table test_explicit_index2 drop constraint col1_exp2_pk drop index;

select index_name, table_name, uniqueness, status
from   user_indexes where table_name = 'TEST_EXPLICIT_INDEX2';

no rows selected.

Problem solved!