Re: How to find the index for a constraint

On Tue, 12 Jun 2001 18:53:59 +0200, Lothar Armbrüster <la_at_oktagramm.de> 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

According to the Application Developer's Guide - Fundamentals:

"If you need to check whether an index is used by a constraint, for example when you want to drop the index, the object number of the index used by a unique or primary key constraint is stored in CDEF$.ENABLED for that constraint. It is not shown in any catalog view. "

Although I'm not clear on how you work back from the "object number" to the index name.

John

--
Got an Oracle database question?
Try the search engine for the database docs at:
http://tahiti.oracle.com/