How to Resolve ORA-25188: cannot drop/disable/defer the primary key constraint for index-organized tables or sorted hash cluster

  • by

ORA-25188

There're some restrictions on using index-organized tables (IOT). This is one case of them.

SQL> alter table ERPAPP.PAY_EXT_P drop constraint XM500P0;
alter table ERPAPP.PAY_EXT_P drop constraint XM500P0
*
ERROR at line 1:
ORA-25188: cannot drop/disable/defer the primary key constraint for index-organized tables or sorted hash cluster

ORA-25188 error message is pretty straight forward, we can't drop, disable defer any IOT's primary key constraint, because it's a required component of an index-organized table.

How to avoid ORA-25188?

You can query ALL_INDEXES in advance to identify IOT.

SQL> select * from all_indexes where index_type = 'IOT - TOP';

The result is a list of IOT, the type is IOT - TOP.

Leave a Reply

Your email address will not be published. Required fields are marked *