Re: Can I rename an index?

From: Sean Dillon <sean.dillon_at_remove.oracle.com>
Date: Fri, 23 Feb 2001 09:57:03 -0800
Message-ID: <IYul6.26$kC2.5569@inet16.us.oracle.com>

You're trying to drop an index that's either your primary key or is a unique constraint on
a table. You need to find the constraint name and drop it. Here's an example of reproducing
the situation:

sys_at_SLAP.US.ORACLE.COM> create table foo(   2 a number,
  3 primary key(a))
  4 /
Table created.

sys_at_SLAP.US.ORACLE.COM> select index name from user indexes where table name = 'FOO';
INDEX NAME

SYS C002598 sys_at_SLAP.US.ORACLE.COM> drop index SYS C002598; drop index SYS C002598

*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

sys_at_SLAP.US.ORACLE.COM> select constraint name   2 from dba constraints
  3 where table name = 'FOO'
  4 /
CONSTRAINT NAME

SYS C002598 sys_at_SLAP.US.ORACLE.COM> alter table foo drop constraint SYS C002597; Table altered.

sys_at_SLAP.US.ORACLE.COM> select * from dba indexes where index name = 'SYS C002597';
no rows selected

sys_at_SLAP.US.ORACLE.COM> alter table foo
  2 add constraint FOO PK
  3 primary key (a)
  4 /
Table altered.

When you go to recreate the index (or perhaps more appropriately the primary key or unique constraint?),
be sure to name it. In my example above, I name my primary key for the FOO table FOO PK. This is pretty
easy to recognize. Your mileage may vary =). Hope this helps!

-- 

Regards,

 smd 
                                      
Sean M. Dillon - Technology Group
Oracle Service Industries
http://www.oracle.com


"Syltrem" <syltrem_at_videotron.ca> wrote in message 
news:n Ai6.8651$QB4.168729_at_wagner.videotron.net...


> I would like to rename an index but the RENAME command is only for 

 tables...


> Tried to drop it and recreate it, but could not do this either...

> 

> SQL> DROP INDEX SYS C0083054

>   2  ;

> DROP INDEX SYS C0083054

> *

> ERROR at line 1:

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

 key


> 

> 

-------------------------------------------------------------------------
---


> 02429, 00000, cannot drop index used for enforcement of unique/primary 

 key


> 

> // *Cause: user attempted to drop an index that is being used as the

> //         enforcement mechanism for unique or primary key.

> // *Action: drop the constraint instead of the index.

> 

-------------------------------------------------------------------------
---


> 

> Now how do I drop a constraint?

> 

> Thanks!

> 

> --

> 

> Sytrem

> http://pages.infinit.net/syltrem

> 

> 

> 


--

Received on Fri Feb 23 2001 - 11:57:03 CST