Skip to content

How Oracle Rename Constraint

  • by

ALTER TABLE RENAME CONSTRAINT

To rename a constraint, we need to do it through ALTER TABLE.

Let's see a constraint.

SQL> show user
USER is "OE"
SQL> select table_name from user_constraints where constraint_name = 'CUST_FNAME_NN';

TABLE_NAME
-----------------------
CUSTOMERS

In this case, we'd like to change the constraint name from CUST_FNAME_NN into CUSTOMER_FNAME_NN as long as the new constraint name does not conflict with others in this schema.

In practice, we don't consider CONSTRAINT as a schema object, so there's no ALTER CONSTRAINT statement in Oracle database.

Next, we issue the command ALTER TABLE RENAME CONSTRAINT.

SQL> alter table customers rename constraint cust_fname_nn to customer_fname_nn;

Table altered.

Let's check the final result.

SQL> select table_name from user_constraints where constraint_name = 'CUSTOMER_FNAME_NN';

TABLE_NAME
-----------------------
CUSTOMERS

Please note that, we can still change the constraint name even if the table is in READ ONLY mode.

Leave a Reply

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