Preserving the Index when dropping the Constraint

A Unique Index that is used to enforce a Unique or Primary Key constraint is sometimes expected to be dropped when the constraint is dropped.
However, this is not the behaviour if the Index pre-dates the constraint -- i.e the index is created before the constraint and the constraint "uses" the existing index. In such a case, since the index definition is not linked to the constraint definition, dropping the constraint does not drop the index.

Here I demonstrate how dropping a constraint does not drop the index because the index was created before the constraint. Also note how the error message (in 10.2.0.4 and below) can be misleading -- it doesn't really state whether an insert fails because of a Unique Index or a Unique Constraint.

SQL> create table my_table (id number, name varchar2(80));

Table created.

SQL> create unique index my_table_uk on my_table(id);

Index created.

SQL> insert into my_table select object_id, object_name from dba_objects;            

50743 rows created.

SQL> commit;

Commit complete.

SQL> alter table my_table add constraint my_table_uk unique (id);

Table altered.

SQL> select constraint_name, constraint_type, index_name from user_constraints where table_name = 'MY_TABLE';  

CONSTRAINT_NAME                C INDEX_NAME
------------------------------ - ------------------------------
MY_TABLE_UK                    U MY_TABLE_UK

SQL> select index_name, uniqueness from user_indexes where table_name = 'MY_TABLE';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
MY_TABLE_UK                    UNIQUE

SQL> alter table my_table drop constraint my_table_uk;

Table altered.

SQL> select constraint_name, constraint_type, index_name from user_constraints where table_name = 'MY_TABLE';

no rows selected

SQL> select index_name, uniqueness, status from user_indexes where table_name = 'MY_TABLE';

INDEX_NAME                     UNIQUENES STATUS
------------------------------ --------- --------
MY_TABLE_UK                    UNIQUE    VALID

SQL> insert into my_table select object_id, object_name from dba_objects;
insert into my_table select object_id, object_name from dba_objects
*
ERROR at line 1:
ORA-00001: unique constraint (HEMANT.MY_TABLE_UK) violated


SQL> 

It isn't necessary that the constraint name and index name be the same. However, if the index name is different from the constraint name, while the index is still preserved after the constraint is dropped, the ORA-00001 error reports the index name :

SQL> create table my_table_2 (id_2 number, name_2 varchar2(80));

Table created.

SQL> create unique index my_t_2_uniq on my_table_2(id_2);

Index created.

SQL> insert into my_table_2 select object_id, object_name from dba_objects;

50743 rows created.

SQL> commit;

Commit complete.

SQL> alter table my_table_2 add constraint my_table_2_uk unique (id_2);

Table altered.

SQL> select constraint_name, constraint_type, index_name from user_constraints where table_name = 'MY_TABLE_2';

CONSTRAINT_NAME                C INDEX_NAME
------------------------------ - ------------------------------
MY_TABLE_2_UK                  U MY_T_2_UNIQ

SQL> select index_name, uniqueness, status from user_indexes where table_name = 'MY_TABLE_2';

INDEX_NAME                     UNIQUENES STATUS
------------------------------ --------- --------
MY_T_2_UNIQ                    UNIQUE    VALID

SQL> alter table my_table_2 drop constraint my_table_2_uk;

Table altered.

SQL> select constraint_name, constraint_type, index_name from user_constraints where table_name = 'MY_TABLE_2';

no rows selected

SQL> select index_name, uniqueness, status from user_indexes where table_name = 'MY_TABLE_2';

INDEX_NAME                     UNIQUENES STATUS
------------------------------ --------- --------
MY_T_2_UNIQ                    UNIQUE    VALID

SQL> insert into my_table_2 select object_id, object_name from dba_objects;
insert into my_table_2 select object_id, object_name from dba_objects
*
ERROR at line 1:
ORA-00001: unique constraint (HEMANT.MY_T_2_UNIQ) violated


SQL>  

You may run similar tests with a Primary Key constraint and see how the Index behaves if the constraint is dropped.

.
.
.