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 multiple fields, but our fields of interest are
To view the details of constraints on a table:
select CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME from USER_CONSTRAINTS where TABLE_NAME='<TABLE_NAME>';
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 (
R) and drop all of the matching 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');
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.