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 of time, we can drop constraint in Oracle using the alter table command. Lets check with the addition of constraint and then dropping them

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'

Now that we have created the constraints,lets see how we can drop the constraints.
Drop the constraint can be simply done by

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

Dropping Foreign Key constraint

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'

Dropping Primary Key constraints

SQL>  alter table emp drop constraint PK_EMP;
 Table altered

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

Also Reads
Primary Key in Oracle
Foreign Key constraint
Unique Key in Oracle
Oracle check constraint