How to drop constraint in Oracle

Constraints are the ways to enforce data integrity rules in the Oracle database. If we dont require constraint at any point in time, we can drop constraint in Oracle using the alter table command. Let’s check with the addition of constraint and then drop them

Oracle Table creation and Constraint creation

SQL> CREATE TABLE "DEPT"
 ("DEPTNO" NUMBER(2,0),
 "DNAME" VARCHAR2(14),
 "LOC" VARCHAR2(13),
  CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
  );
 Table created.

 SQL> CREATE TABLE "EMP"
   ( "EMPNO" NUMBER(4,0),
   "ENAME" VARCHAR2(10) Not null,
   "JOB" VARCHAR2(9) not null,
   "MGR" NUMBER(4,0),
   "HIREDATE" DATE,
   "SAL" NUMBER(7,2),
   "COMM" NUMBER(7,2),
   "DEPTNO" NUMBER(2,0),
    CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO"),
    CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
    REFERENCES "DEPT" ("DEPTNO") ENABLE);
 Table created.

 SQL> col CONSTRAINT_NAME  format a20
 SQL> col INDEX_NAME format a20
 SQL> col CONSTRAINT_TYPE format a5
 SQL> select CONSTRAINT_NAME,INDEX_NAME,CONSTRAINT_TYPE from user_constraints where TABLE_NAME='&1';
 Enter value for 1: EMP
 old   1: select CONSTRAINT_NAME,INDEX_NAME,CONSTRAINT_TYPE from user_constraints where TABLE_NAME='&1'
 new   1: select CONSTRAINT_NAME,INDEX_NAME,CONSTRAINT_TYPE from user_constraints where TABLE_NAME='EMP'

Here we have created two tables and created constraints for the primary key, foreign key, and check constraint. Now that we have created the constraints, let’s see how we can drop the constraint in oracle
Drop the constraint can be simply done by

alter table <table name> drop constraint <constraint name>;

We can drop primary key constraint, unique key constraint, foreign key constraint, check constraint and non-null constraint using the same command given above. We just need to first find all the constraints on the table and then drop them according to the need.

drop foreign key constraint oracle

SQL> alter table emp drop constraint FK_DEPTNO;
 Table altered.

SQL> col CONSTRAINT_NAME  format a20
SQL> col INDEX_NAME format a20
SQL> col CONSTRAINT_TYPE format a5
SQL> select CONSTRAINT_NAME,INDEX_NAME,CONSTRAINT_TYPE from user_constraints where TABLE_NAME='&1';
Enter value for 1: EMP
old   1: select CONSTRAINT_NAME,INDEX_NAME,CONSTRAINT_TYPE from user_constraints where TABLE_NAME='&1'
new   1: select CONSTRAINT_NAME,INDEX_NAME,CONSTRAINT_TYPE from user_constraints where TABLE_NAME='EMP'

drop primary key constraint in oracle

SQL>  alter table emp drop constraint PK_EMP;
 Table altered

We can also drop the Primary key constraint by specifying the column name

SQL> alter table emp drop primary key ;
Table altered

Important Note
You cannot drop a primary key or unique key constraint that is part of a referential integrity constraint without also dropping the foreign key. To drop the referenced key and the foreign key together, use the CASCADE  clause. If you omit CASCADE, then Oracle Database does not drop the primary key or unique constraint if any foreign key references it.

Drop Not null or check constraints

SQL> desc emp

Now Dropping the Not Null constraints

SQL>  alter table emp drop constraint SYS_C00541121 ;
 Table altered.

SQL> desc emp

drop unique constraint oracle

SQL>CREATE TABLE TECH_TABLE (
Name VARCHAR(50) NOT NULL,
Address VARCHAR(15),
CONSTRAINT NAME_UK UNIQUE(Name));

Table created.

Now the table is created, we can drop the constraint using the below statement

SQL> ALTER TABLE TECH_TABLE DROP CONSTRAINT NAME_UK;
Table altered.

We can drop the unique constraint by specifying the column name also

ALTER TABLE TECH_TABLE DROP UNIQUE (NAME)
Table altered.

Drop all the constraints in Oracle

You can drop all the constraints in the table using the below procedure also

begin
for r in ( select table_name, constraint_name
from user_constraints
where table_name='&1' )
loop
execute immediate 'alter table '|| r.table_name
||' drop constraint '|| r.constraint_name;
end loop;
end;

OR

select 'alter table '||owner||'.'||table_name||' drop constraint '||CONSTRAINT_NAME||' ;' from dba_constraints
where owner='&owner_name' and table_name ='&table_name';

We learn how to drop constraints in oracle. The constraint could be a primary key, Unique key, foreign key or check constraint.
I hope you like this article and it helps in your day-to-day work. Please let me know your feedback on this.

Also Reads
drop primary key in oracle: primary key in oracle uniquely identify the row in the table. It cannot be null & can be created at the time of table creation or after the table is created
Foreign Key constraint: Foreign Key in Oracle is to enforce data integrity between multiple tables. It can be added at the time of table creation and after the table is created also.
Unique Key in Oracle: Unique key enforces unique in the column in the table and helps us identify the row quickly. Oracle create the unique index for the key if no index is available
Oracle check constraint: Oracle Check Constraints are used to enforce integrity rules based on logical expressions, such as comparisons. The check condition must return true or false
Coalesce Function in Oracle: Coalesce function in oracle will return the first expression if it is not null else it will do coalesce the rest of the expression.
how to check all constraints on a table in oracle: how to check all constraints on a table in oracle using dba_constraints and dba_cons_columns data dictionary views in Oracle database
oracle drop table if exists: Check how to check if table exists and then drop the table in oracle
Drop Oracle Index if exists: check out this post to learn about How to Drop the index in Oracle and how to drop the unique index in oracle
https://docs.oracle.com/cd/B10500_01/appdev.920/a96590/adg05itg.htm