Rename Constraint

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>

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.