How to Drop Constraints Without Knowing Its Name in Oracle

Introduction

Dropping or deleting constraints from a table is a common use case while dealing with database migrations and upgrades.

With named constraints, it is as easy as just executing the alter table command with the specific constraint name, but when you don't know the names (system-generated names), it becomes a bit painful.

View Details of Constraints on a Table in Oracle

Dropping constraints without knowing its name is a bit tricky in Oracle. The constraint details are stored in a table called USER_CONSTRAINTS.  

The table USER_CONSTRAINTS multiple fields, but our fields of interest are CONSTRAINT_NAME, CONSTRAINT_TYPE and TABLE_NAME.

To view the details of constraints on a table:

select CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME from USER_CONSTRAINTS where TABLE_NAME='<TABLE_NAME>';

The CONSTRAINT_TYPE could be any of the following:

C - indicates column constraints like Not null, unique, etc.

P - indicates primary key constraints.

R - indicates foreign key constraints.

Drop un-named Constrains in Oracle

To drop constraints without knowing the name, we have to first find the name of the constraint by querying the USER_CONSTRAINTS as explained above.

Once we have the name of the constraint, drop it by executing the alter command:

alter table <TABLE_NAME> drop constraint <CONSTRAINT_NAME>

Instead of having the specify the constraint name, we can create a stored procedure that takes the table name and type of the constraint (C, P or R) and drop all of the matching constraints.


CREATE OR REPLACE PROCEDURE DROP_TABLE_CONSTRAINTS(V_TABLE_NAME IN varchar2, V_CONSTRAINT_TYPE IN varchar2) IS
    V_CONSTRAINT_NAME VARCHAR2(30);
    sql_stmt VARCHAR2(2048);
    CURSOR cur IS
          SELECT DISTINCT CONSTRAINT_NAME
          FROM USER_CONSTRAINTS 
          WHERE TABLE_NAME = UPPER(V_TABLE_NAME) AND CONSTRAINT_TYPE=V_CONSTRAINT_TYPE;
BEGIN
    OPEN cur;
    LOOP
    FETCH cur INTO V_CONSTRAINT_NAME;
    EXIT WHEN cur%notfound;
    sql_stmt := 'ALTER TABLE ' || V_TABLE_NAME || ' DROP CONSTRAINT ' || V_CONSTRAINT_NAME;
    execute immediate sql_stmt;
    DBMS_OUTPUT.PUT_LINE('Dropping constraint');
    END LOOP;
    COMMIT;
END DROP_TABLE_CONSTRAINTS;
/
Store procedure to drop table constraints

Now to drop all color constraints from a table run:

exec DROP_TABLE_CONSTRAINTS('<TABLE_NAME>', 'C');

To drop all primary key constraints:

exec DROP_TABLE_CONSTRAINTS('<TABLE_NAME>', 'P');

To drop all foreign key constraints:

exec DROP_TABLE_CONSTRAINTS('<TABLE_NAME>', 'R');

Conclusion

There are several ways to drop constraints in the Oracle database. We have gone over few simple approaches and highlight a stored procedure to drop a constraint without knowing its name.

Asha

Asha

I am a self mentored web enthusiast who likes to inspect element everything I see on the web.