The RENAME_CONSTRAINT stored procedure is used to rename a primary key or foreign key constraint.
| Parameter | Description | |
|---|---|---|
| SCHEMA_NAME | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | INUSER | |
| Description | The schema name that owns the table with the constraint being renamed. | |
| TABLE_NAME | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | None | |
| Description | The name of the table with the constraint being renamed. | |
| CONSTRAINT_NAME | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | None | |
| Description | The current name of the constraint being renamed. | |
| NEW_NAME | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | NULL | |
| Description | The new name for the constraint being renamed. | |
Exceptions
Examples
EXEC IN_DB_UTIL.RENAME_CONSTRAINT('INUSER', [TABLE_NAME], [CURRENT_NAME], [NEW_NAME]);
SET SERVEROUTPUT ON;
DECLARE
V_TABLE_NAME VARCHAR2(128);
V_CURRENT_NAME VARCHAR2(128);
V_NEW_NAME VARCHAR2(128);
-- Exceptions
NAME_UNAVAILABLE EXCEPTION; PRAGMA EXCEPTION_INIT(NAME_UNAVAILABLE, -20002);
INVALID_OBJECT EXCEPTION; PRAGMA EXCEPTION_INIT(INVALID_OBJECT, -20006);
BEGIN
V_TABLE_NAME := 'IN_AUDIT_OBJ';
V_CURRENT_NAME := 'FK_AUDITOBJECT_AUDIT_ID';
V_NEW_NAME := 'FK_AO_A_ID';
IN_DB_UTIL.RENAME_CONSTRAINT('INUSER', V_TABLE_NAME, V_CURRENT_NAME, V_NEW_NAME);
EXCEPTION
WHEN INVALID_OBJECT THEN NULL;
WHEN NAME_UNAVAILABLE THEN NULL;
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('There was an unhandled exception.');
END;
/
Example Output
SQL> EXEC IN_DB_UTIL.RENAME_CONSTRAINT('INUSER', 'IN_AUDIT', 'PK_AUDIT', 'PK_NEW_NAME');
ALTER TABLE INUSER.IN_AUDIT RENAME CONSTRAINT PK_AUDIT TO PK_NEW_NAME
The constraint has been renamed.
PL/SQL procedure successfully completed.