Re: How to find the index for a constraint

Lothar Armbrüster wrote:

> Hello out there,
>
> I'm looking for a method to find the index name for a, say, primary key
> constraint. Normally the index is named like the constraint, but today I
> came upon a table where the constraint seemed to use another index.
> I wanted to drop an index but Oracle said this index could not be
> dropped:
>
> ORA-02429 cannot drop index used for enforcement of unique/primary key
>
> But this index had the PK column and one additional column. The index
> named like the constraint also existed.
> After I did "alter table ... drop primary key" I could drop the index.
> I wasn't droped with the constraint.
>
> I look for a data dictionary view which shows me the connection between
> index and constraint, but I cannot find one which contains INDEX_NAME
> and CONSTRAINT_NAME.
>
> The system ist Oracle 8.1.7 on NT4.
>
> Many thanks in advance,
> Lothar
>
> --
> Lothar Armbrüster | la_at_oktagramm.de
> Hauptstr. 26 | la_at_heptagramm.de
> D-65346 Eltville | lothar.armbruester_at_t-online.de

Using user_constraints, user_con_columns, user_indexes, and user_ind_columns you are looking for a unique index where the columns in the index match the columns in the primary key constraint.

Daniel A. Morgan Received on Tue Jun 12 2001 - 17:33:28 CDT