Oracle9i New Feature Series: Rename Table Constraints

Oracle9i Release 2 lets you rename the constraint names. Here is a demonstration:

SQL> create table b_test (col1 number, col2 number,

  2* constraint pk_b_test primary key (col1))

SQL> /

 

Table created.

 

SQL> select constraint_name from dba_constraints

  2  where table_name = 'B_TEST';

 

CONSTRAINT_NAME

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

PK_B_TEST

 

1 row selected.

 

SQL> select index_name from dba_indexes

  2  where table_name = 'B_TEST';

 

INDEX_NAME

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

PK_B_TEST

 

1 row selected.

 

SQL> alter index PK_B_TEST rename to B_TEST_PK;

 

Index altered.

 

SQL> select constraint_name from dba_constraints

  2  where table_name = 'B_TEST';

 

CONSTRAINT_NAME

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

PK_B_TEST

 

1 row selected.

 

SQL> alter table b_test rename constraint PK_B_TEST to B_TEST_PK;

 

Table altered.

 

SQL> select constraint_name from dba_constraints

  2  where table_name = 'B_TEST';

 

CONSTRAINT_NAME

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

B_TEST_PK

 

1 row selected.

 

SQL>

What if you try to do the same in an Oracle8i database?

SQL> create table b_test (col1 number, col2 number,

  2  constraint pk_b_test primary key (col1))

  3  /

 

Table created.

 

SQL> select constraint_name from dba_constraints

  2  where table_name = 'B_TEST';

 

CONSTRAINT_NAME

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

PK_B_TEST

 

1 row selected.

 

SQL> select index_name from dba_indexes

  2  where table_name = 'B_TEST';

 

INDEX_NAME

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

PK_B_TEST

 

1 row selected.

 

SQL> alter index PK_B_TEST rename to B_TEST_PK;

 

Index altered.

 

SQL> select constraint_name from dba_constraints

  2  where table_name = 'B_TEST';

 

CONSTRAINT_NAME

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

PK_B_TEST

 

1 row selected.

 

SQL> alter table b_test rename constraint PK_B_TEST to B_TEST_PK;

alter table b_test rename constraint PK_B_TEST to B_TEST_PK

                          *

ERROR at line 1:

ORA-14155: missing PARTITION or SUBPARTITION keyword

 

 

SQL>