Re: How to find the index for a constraint

Daniel,

If you review the question, you will note that

1. The system is 8.1.7, which means the index supporting the primary key does not have to be a unique index
2. Lothar points out that the columns in the index being used by Oracle do NOT match the columns in the primary key, they are a superset of the column.

A primary key constraint on (colA, colB) could be supported by a non-unique index on (colA, colB, colD) or a non-unique index on (colB, colA, colC).

In fact, as other users have discovered, you can find that when you do an export/import, the index used to support a primary key may change - even to the extent that the index that you expect to see supporting the primary key may not even be recreated.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Daniel A. Morgan wrote in message <3B2698B8.387AC3CE_at_attws.com>...


>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

>