ORA-02429: cannot drop index used for enforcement of unique/primary key

This error condition is easily explained:

create table test1(
test1_id number,
test1_parent_id number,
test1_child_id number,
col1 varchar2 (30),
constraint test1_pk primary key (test1_id)
)
/

drop index test1_pk
/

ORA-02429: cannot drop index used for enforcement of unique/primary key

I attempted to drop an index (whose name is the same of the associated constraint) and Oracle prevents me from doing that.

But there can be a subtler situation.

Let's create the following two tables and a non-unique index, supposing that we initially want this index for performance reasons.

create table test1(
test1_id number,
test1_parent_id number,
test1_child_id number,
col1 varchar2 (30),
constraint test1_pk primary key (test1_id)
)
/
create index idx_test1 on test1( test1_parent_id, test1_child_id)
/

Later on we realize that it makes sense to make that column pair a unique key so we add the following constraint on the first table (the data in the table must be consistent or we will get an exception...).

alter table test1 add constraint test1_uq unique (test1_parent_id, test1_child_id)
/

If we inspect the dictionary view USER_CONSTRAINTS, you will see that Oracle didn't create a new index to enforce this unique constraint, but recycled the existing index that we allegedly created for performance reasons.

select owner, constraint_name, index_owner,index_name
from user_constraints
where table_name = 'TEST1'

OWNER	CONSTRAINT_NAME	INDEX_OWNER	INDEX_NAME
TEST	TEST1_UQ	TEST	IDX_TEST1

Clearly, if we attempt to drop the index, ORA-02429 is raised.

This is just to demonstrate that this exception has nothing to do with the constraint and the associated index having the same name or being built at the same time.

See message translations for ORA-02429 and search additional resources.

---

ORA-02429: impossibile eliminare indice usato per imposizione di chiave unica/primaria
ORA-02429: no se puede borrar el índice utilizado para el forzado de clave única/primaria
ORA-02429: no es pot eliminar un índex que controla la unicitat de la clau, o la clau primària
ORA-02429: impossible abandonner index utilisé pour utilisation de clé primaire/unique
ORA-02429: Index zum Erzwingen des eindeutigen/Primärschlüssels nicht löschbar
ORA-02429: δεν είναι δυνατή η διαγραφή ευρετηρίου χρησιμοποιούμενο για την επιβολή μοναδικού/πρωτεύοντος κλειδιού
ORA-02429: indeks, som anvendes til forcering af unik/primær nøgle, kan ikke droppes
ORA-02429: kan inte ta bort index som används för tillämpning av unik/primär nyckel
ORA-02429: kan ikke fjerne indeks som brukes til gjennomføring av entydig/primær nøkkel
ORA-02429: ei voi poistaa indeksiä, jota käytetään vahvistamaan yksikäsitteistä avainta tai perusavainta
ORA-02429: az egyediséget/elsődlegességet biztosító index nem dobható el
ORA-02429: nu se poate elimina indexul utilizat pt punerea în val a cheii unice/primare
ORA-02429: Kan index niet verwijderen: gebruikt voor afdwingen unieke/primaire sleutel.
ORA-02429: não é possível eliminar o índice usado para imposição da chave exclusiva/primária
ORA-02429: não é possível suprimir índice utilizado para imposição de chave exclusiva/primária
ORA-02429: нельзя удалить индекс, использованный для реализации уникальн./первичн. ключа
ORA-02429: nelze zrušit index používaný pro prosazení jednoznačného/primárního klíče
ORA-02429: nemožno zrušiť index použitý pre implementáciu jedinečného alebo primárneho kľúča
ORA-02429: nie można usunąć indeksu odpowiedzialnego za klucz unikatowy/główny
ORA-02429: benzersiz/birincil anahtarların zorlanması için kullanılan dizin bırakılamaz
ORA-02429: cannot drop index used for enforcement of unique/primary key