Re: Can I rename an index?

You don't need to drop the entire constraint. Disabling it will do the deed -and if the index used happened to be a unique one (it *is* possible to enforce primary keys and unique constraints with non-unique indexes), then the disablement of the constraint will drop the index automatically for you.

If it's a non-unique index, then the index is retained after disabling the constraint, and would have to be dropped manually.

If you've got 8i, then there's one further subtle twist. The problem of disabling a primary key constraint is that the table remains open for business for Users -and clearly, the possibility arises that they could use the time when the constraint is not in force to load all sorts of nonsense into the table. In 8i, therefore, you can 'disable validate' the constraint, which allows you to drop the index as before, but locks the table from any DML that would violate the constraint. The table data is thus guaranteed to be clean when it comes time to re-enable the constraint.

The basic syntax is therefore 'alter table blah disable constraint constraint_name'. If you're using the 8i new tricks, it's 'alter table blah disable validate constraint constraint_name'.

Regards
HJR "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:t8m1pasms7a260_at_beta-news.demon.nl...
>
> "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
> >
> >
> >
>
> alter table <table_name> drop constraint <constraint_name>
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
>
>
Received on Wed Feb 14 2001 - 19:09:13 CST