Re: Renaming primary and /or unique keys

Jens Lenge schrieb:
> Hello world,
>
> I discovered a strange behavior when renaming primary and/or unique
> keys of an existing table in Oracle 9i (or maybe it's rather normal
> than strange, and I am simply doing something wrong).
>
> Normally, creating a primary key or unique key constraint will
> automatically also create an index with the same name and reference.
>
> I have tried to rename an existing primary key and an existing unique
> key like that:
>
> alter table mytable rename constraint pk111 to pk_mytable_id;
> alter table mytable rename constraint uk222 to uk_mytable_name;
>
> While the two CONSTRAINTS now do have the new names, the corresponding
> INDICES still have the old names.
>
> How can I rename both the constraints AND the corrsponding indices?
>
> I cannot simply rename the indices to the new names (as the destination
> name is already used), and I also cannot delete them (as they enforce a
> primary resp. unique key). So how to go?
>
> Jens
>

Tables, indexes and constraints resides in different namespaces, which means, you can have a table , index and constraint named the same. Hence , you can rename your index and constraints to the same identifier.

sqlplus scott/tiger

SQL*Plus: Release 9.2.0.6.0 - Production on Do Jul 13 15:47:25 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0 - Production

SQL> create table my_emp as select * from emp;

Table created.

SQL> create index my_emp on my_emp(empno);

Index created.

SQL> alter table my_emp add constraint my_emp check(deptno<100);

Table altered.

SQL> Best regards

Maxim Received on Thu Jul 13 2006 - 14:50:27 CDT