Re: Renaming primary and /or unique keys

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 13 Jul 2006 22:15:15 +0200
Message-ID: <e969kf$uat$03$1@news.t-online.com>

Jens Lenge schrieb:
> Maxim Demenko wrote:
>

>> SQL> create table my_emp as select * from emp;
>> SQL> create index my_emp on my_emp(empno);
>> SQL> alter table my_emp add constraint my_emp check(deptno<100);

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

Why should it be an exception ?

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 Received on Thu Jul 13 2006 - 15:15:15 CDT