26 July, 2010

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.

.
.
.

5 comments:

Narendra said...

And from 10g onwards, the ALTER TABLE..DROP CONSTRAINT also provides an option to KEEP INDEX. So even if the index was created as a result of adding constraint (and is not pre-dated), one can still decide to keep the index after dropping the constraint.

Roberto said...

The index need not necessarily be UNIQUE. Obviously, however, in this case (NONUNIQUE) without constraint there may be duplication.

Hemant K Chitale said...

Narendra,

Yes, the ALTER TABLE ... DROP CONSTRAINT does have a "KEEP INDEX" clause.

Hemant

Hemant K Chitale said...

Roberto,

Yes, an Index used to enforce a Constraint does not have to be Unique.

In fact, a Unique Constraint can be created without an Index as well -- use "ALTER TABLE tablename ADD CONSTRAINT constraintname UNIQUE (key) DISABLE VALIDATE"


Hemant

Anonymous said...

Thank you ... so much