Re: How to find the index for a constraint

From: Mark J. Bobak <mark.NOSPAM_at_bobak.net>
Date: Wed, 13 Jun 2001 04:25:49 GMT
Message-ID: <20010613.002549.1605908235.6955@bobak.net>

In article <3B2698B8.387AC3CE_at_attws.com>, "Daniel A. Morgan" <Daniel.Morgan_at_attws.com> wrote:

> 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.

Or, you may want to try:
undef tablename
undef owner
select o.name indexname,

       c.con#,
       con.name constraintname
  from obj$ o,
       cdef$ c,
       con$ con

 where o.obj# = c.enabled
   and con.con#=c.con#
   and c.type# = 2 /* primary key */
   and c.obj# =(select obj#

                  from obj$
                 where name = '&tablename'
                   and type#=2
                   and owner#=(select user#
                                 from user$
                                where name='&owner'
                              )
               )

/

Given a TABLE_NAME and OWNER, this will find the name of the PRIMARY KEY constraint, as well as the unique index that's enforcing it.

-Mark Received on Tue Jun 12 2001 - 23:25:49 CDT