Oracle Rename Constraint

To rename a constraint in the Oracle database, we can use ALTER … RENAME with Constraint name. Syntax to rename the constraint,

ALTER TABLE tableName
RENAME CONSTRAINT oldConstraintName TO newConstraintName;

In the Oracle database, whenever we are creating constraint then the oracle server internally automatically generates a unique identification number for identifying a constraint uniquely in the format of SYS_Cn, this is also called as pre-defined constraint name.

SQL> CREATE TABLE test (
       sno NUMBER(10) UNIQUE,
       name VARCHAR2(10) UNIQUE
     );
Table created.

SQL> INSERT INTO test VALUES(1, 'abc');
1 row created.

SQL> INSERT INTO test VALUES(1, 'xyz');
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0014205) violated

SQL> INSERT INTO test VALUES(2, 'abc');
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0014206) violated

Here SYS_C0014205 and SYS_C0014206 are the Oracle generated unique identification number of constraint applied on sno and name columns of test table. We can get this information from user_cons_columns.

SQL> DESC user_cons_columns;
 Name              Null?    Type
 ----------------- -------- --------------
 OWNER             NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME   NOT NULL VARCHAR2(30)
 TABLE_NAME        NOT NULL VARCHAR2(30)
 COLUMN_NAME                VARCHAR2(4000)
 POSITION                   NUMBER

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'TEST';

COLUMN_NAME     CONSTRAINT_NAME
-----------     ---------------
SNO             SYS_C0014205
NAME            SYS_C0014206

Note that in the above query table name TEST is given in the capital letter. Oracle table name, column names are not case sensitive but records of a table are case sensitive, and here TEST is a record of user_cons_columns.

The pre-defined constraint name can create confusion for others and just by seeing the pre-defined constraint name, we can’t identify which constraint is enabled on this column. We can rename the pre-defined constraint name in the Oracle database.

Syntax to rename the constraint in Oracle,
ALTER TABLE tableName
RENAME CONSTRAINT oldConstraintName TO newConstraintName;

Example:-

SQL> ALTER TABLE test
     RENAME CONSTRAINT SYS_C0014364 TO unique_test;
Table altered.

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'TEST';

COLUMN_NAME     CONSTRAINT_NAME
-----------     ---------------
SNO             UNIQUE_TEST
NAME            SYS_C0014206

When Constraint is enabled at table level then the pre-defined constraint name will be same of those columns.

Assigning User-defined Name to Constraint

In place of pre-defined constraint name we can also assign our own name by using CONSTRAINT keyword by using following syntax. This is called user defined constraint name.

Syntax:-
CONSTRAINT user-definedname constriantType

Example:-

SQL> CREATE TABLE test2 (
       sno NUMBER(10) CONSTRAINT unique_sno UNIQUE,
       name VARCHAR2(10)
     );
Table created.

SQL> ALTER TABLE test2
     ADD CONSTRAINT unique_name UNIQUE(name);
Table altered.

SQL> ALTER TABLE test2
     ADD address VARCHAR2(10) 
     CONSTRAINT unique_address UNIQUE;
Table altered.

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'test2';
COLUMN_NAME    CONSTRAINT_NAME
-------------- --------------
SNO            UNIQUE_SNO
NAME           UNIQUE_NAME
ADDRESS        UNIQUE_ADDRESS

Now, we can drop these constraints by the same syntax, ALTER TABLE tablename DROP CONSTRAINT constraintName;

Note:- User-defined constraint name must be unique within a user else Oracle gives error:- ORA-02264: name already used by an existing constraint

SQL> CREATE TABLE test3 (
       sno number(10) 
       CONSTRAINT unique_sno UNIQUE
     );
ORA-02264: name already used by an existing constraint

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Also Learn,

Leave a Comment

Your email address will not be published. Required fields are marked *