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