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.