ORA-02429: cannot drop index used for enforcement of unique/primary key ORA-29862: cannot specify FORCE option for dropping non-domain index

Cause:

We cannot drop an index or non domain index with a force option dependent on a constraint for a table

12:08:28 kIsH@x3z6zx9<^>drop index SYS_C0011370;
drop index SYS_C0011370
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key


Elapsed: 00:00:00.02
12:08:41 kIsH@x3z6zx9<^>drop index SYS_C0011370 force;
drop index SYS_C0011370 force
*
ERROR at line 1:
ORA-29862: cannot specify FORCE option for dropping non-domain index


Elapsed: 00:00:00.01

Action:

Check the constraint which hold the index

12:11:55 kIsH@x3z6zx9<^>col OWNER for a20
12:12:03 kIsH@x3z6zx9<^>col TABLE_NAME for a20
12:12:05 kIsH@x3z6zx9<^>col CONSTRAINT_NAME for a20
12:12:07 kIsH@x3z6zx9<^>col INDEX_NAME for a20
12:16:38 kIsH@x3z6zx9<^>select owner,table_name,constraint_name,index_name,status,search_condition from all_constraints where table_name='XTBL';

OWNER                TABLE_NAME           CONSTRAINT_NAME      INDEX_NAME           STATUS   SEARCH_CONDITION
-------------------- -------------------- -------------------- -------------------- -------- --------------------
SYS                  XTBL                 SYS_C0011369                              ENABLED  "XDATE" IS NOT NULL
SYS                  XTBL                 SYS_C0011370         SYS_C0011370         ENABLED

Disable and drop the constraint

12:16:45 kIsH@x3z6zx9<^>alter table xtbl disable constraint SYS_C0011370;

Table altered.

Elapsed: 00:00:00.09
12:17:39 kIsH@x3z6zx9<^>alter table xtbl drop constraint SYS_C0011370;

Table altered.

Elapsed: 00:00:00.02

Oracle automatically drops the index for you similar to how it created with the constraints

12:17:48 kIsH@x3z6zx9<^>drop index SYS_C0011370;
drop index SYS_C0011370
           *
ERROR at line 1:
ORA-01418: specified index does not exist

12:18:07 kIsH@x3z6zx9<^>select owner,table_name,constraint_name,index_name,status,search_condition from all_constraints where table_name='XTBL';

OWNER                TABLE_NAME           CONSTRAINT_NAME      INDEX_NAME           STATUS   SEARCH_CONDITION
-------------------- -------------------- -------------------- -------------------- -------- --------------------
SYS                  XTBL                 SYS_C0011369                              ENABLED  "XDATE" IS NOT NULL

Leave a Reply