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

Malcolm Dew-Jones schrieb:

Could you please post a short SQL example for another $0,10? ;o)

(Or maybe a cold beer next time you visit Germany?)

Jens

Jens Lenge schrieb:
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

Maxim Demenko wrote:

So far it's clear, but that does not involve renaming an existing
primary key and index.

I was out for an example of how to disable/reenable and rename both the
constraint and index as you originally suggested.

Jens

Jens Lenge (spampot (AT) gmx (DOT) net) wrote:
: 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?

you could disable the constraint first and then change things and then
reanable the constraint afterwards.

$0.10

Jens Lenge schrieb:

SQL> drop table my_emp;

Table dropped.

SQL> create table my_emp as select * from emp;

Table created.

SQL> alter table my_emp add primary key(empno);

Table altered.

SQL> select constraint_name from cons where table_name = 'MY_EMP';
select constraint_name from cons where table_name = 'MY_EMP'
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select constraint_name from user_constraints where table_name =
'MY_EMP' and constraint_type = 'P';

CONSTRAINT_NAME
------------------------------
SYS_C002783

SQL> select index_name from user_indexes where table_name = 'MY_EMP';

INDEX_NAME
------------------------------
SYS_C002783

SQL> alter table my_emp rename constraint SYS_C002783 to my_emp_pk;

Table altered.

SQL> alter index SYS_C002783 rename to my_emp_pk;

Index altered.

SQL> select status from user_indexes where index_name = 'MY_EMP_PK';

STATUS
--------
VALID

SQL> select status from user_constraints where constraint_name =
'MY_EMP_PK';

STATUS
--------
ENABLED

SQL>

Best regards

Maxim

Maxim Demenko wrote:
I have just tried to create a new index for the existing primary key
constraint (using Oracle SQL Developer):

=> Error: Name pk_mytable_id is already used by table mytable.

Jens Lenge wrote:
To be more specific, disabling the constraint only works as long as it
is not referenced by other tables. But when I try to disable my primary
key, I get an error saying it cannot be disabled because it is
referenced...

Just did it - your example was the solution!
I just have to directly use "alter index" instead of
disabling/re-enabling the primary key.

Thank you very much for your help.

Jens