Drop Constraint In Oracle

We can drop constraint in Oracle database by using constraint-name. The constraint name can be generated by Oracle database or user-defined name.

Syntax to drop constraint in Oracle,
ALTER TABLE tablename
DROP CONSTRAINT constraintName;

But there is a separate story for NOT NULL constraint and PRIMARY KEY constraint. We can’t use above syntax for NOT NULL constraint. The Primary key constraint can be dropped using above syntax but there is also an another way.

Drop NOT NULL Constraint in Oracle

We can drop NOT NULL constraint in Oracle database from an existing column but that column must not be used in a PRIMARY KEY constraint.

In Oracle, when column is changed to allow nulls then Oracle database automatically drop the NOT NULL constraints.

Syntax to allow NULL value on an existing column:-
ALTER TABLE tablename
MODIFY(columnName NULL);

Example to remove NOT NULL contraints from sno and address columns of “nulltest” table:-

SQL> CREATE TABLE nulltest (
       sno NUMBER(10) NOT NULL,
       name VARCHAR2(15) NOT NULL
     );
Table created.

SQL> DESC nulltest;
 Name     Null?    Type
 -------- -------- ------------
 SNO      NOT NULL NUMBER(10)
 NAME     NOT NULL VARCHAR2(15)

SQL>  ALTER TABLE nulltest 
      MODIFY (sno NULL, name NULL);
Table altered.

SQL> DESC nulltest;
 Name     Null?    Type
 -------- -------- ------------
 SNO               NUMBER(10)
 NAME              VARCHAR2(15)

Drop PRIMARY KEY Constraint in Oracle

There can be only one PRIMARY KEY constraint on a table, so dropping PRIMARY KEY constraint is very easy.

Syntax:-
ALTER TABLE tableName
DROP PRIMARY KEY

Using above syntax we can drop PRIMARY KEY constraint at column level and also at table level. Example:-

SQL> CREATE TABLE primarytest (
       sno NUMBER(10) PRIMARY KEY
     );
Table created.

SQL> ALTER TABLE primarytest 
     DROP PRIMARY KEY;
Table altered.

Note:- Generally, we can’t drop PRIMARY KEY constraint along with REFERENCED KEY. To overcome this problem, oracle provided CASCADE clause along with ALTER DROP, which is used to drop primary key constraint. Syntax:- ALTER TABLE tableName DROP PRIMARY KEY CASCADE;

Syntax:-
ALTER TABLE tablename
DROP PRIMARY KEY CASCADE;

Example:-

SQL> CREATE TABLE master1 (
       sno NUMBER(10) PRIMARY KEY
     );
Table created.

SQL> CREATE TABLE child1 (
       sno NUMBER(10)
       REFERENCES master1
     );
Table created.

SQL> ALTER TABLE master1
     DROP PRIMARY KEY;
ERROR:- ORA-02273: this unique/primary key 
is referenced by some foreign keys

SQL> ALTER TABLE master1
     DROP PRIMARY KEY CASCADE;
Table altered.

Drop Constraint by Constraint Name

In oracle database, whenever we are creating constraint then oracle server internally automatically generates an unique identification number for identifying a constraint uniqually in the format of SYS_Cn, this is also called as pre-defined constraint name.

Using predefined constraint name we can drop UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK constraint (but not NOT NULL constraint).

SQL> CREATE TABLE test (
       sno NUMBER(10) UNIQUE,
       name VARCHAR2(10) UNIQUE
     );
Table created.

SQL> INSERT INTO test VALUES(1, 'abc');
1 row created.

SQL> INSERT INTO test VALUES(1, 'xyz');
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0014205) violated

SQL> INSERT INTO test VALUES(2, 'abc');
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0014206) violated

Here SYS_C0014205 and SYS_C0014206 are the Oracle generated unique identification number of constraint applied on sno and name columns of test table. We can get this information from user_cons_columns.

SQL> DESC user_cons_columns;
 Name              Null?    Type
 ----------------- -------- --------------
 OWNER             NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME   NOT NULL VARCHAR2(30)
 TABLE_NAME        NOT NULL VARCHAR2(30)
 COLUMN_NAME                VARCHAR2(4000)
 POSITION                   NUMBER

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'TEST';

COLUMN_NAME     CONSTRAINT_NAME
-----------     ---------------
SNO             SYS_C0014205
NAME            SYS_C0014206

Note that in the above query table name TEST is given in the capital letter. Oracle table name, column names are not case sensitive but records of a table is case sensitive, and here TEST is a record of user_cons_columns.

To drop the constraint we can use these constraint names,

SQL> ALTER TABLE test 
     DROP CONSTRAINTS SYS_C0014205;
Table altered.

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'TEST';
COLUMN_NAME     CONSTRAINT_NAME
-----------     ---------------
NAME            SYS_C0014206

When Constraint is enabled at table level then the pre-defined constraint name will be same of those columns.

SQL> CREATE TABLE test1 (
       sno NUMBER(10),
       name VARCHAR2(10),
       address VARCHAR2(10),
       UNIQUE(sno, name)
     );
Table created.

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'TEST1';
COLUMN_NAME     CONSTRAINT_NAME
-----------     ---------------
SNO             SYS_C0014207
NAME            SYS_C0014207

SQL> ALTER TABLE test1
     DROP CONSTRAINTS SYS_C0014207;
Table altered.

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'TEST1';
no rows selected

Assigning User-defined Name to Constraint

While dropping the constraints, the pre-defined constraint name can create confusion for others, and just by seeing the pre-defined constraint name, we can’t identify which constraint is enabled on this column. In place of a pre-defined constraint name, we can also assign our own name by using the CONSTRAINT keyword by using the following syntax. This is called a user-defined constraint name.

Syntax:-
CONSTRAINT user-definedname constriantType

Example:-

SQL> CREATE TABLE test2 (
       sno NUMBER(10) CONSTRAINT unique_sno UNIQUE,
       name VARCHAR2(10)
     );
Table created.

SQL> ALTER TABLE test2
     ADD CONSTRAINT unique_name UNIQUE(name);
Table altered.

SQL> ALTER TABLE test2
     ADD address VARCHAR2(10) 
     CONSTRAINT unique_address UNIQUE;
Table altered.

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'test2';
COLUMN_NAME    CONSTRAINT_NAME
-------------- --------------
SNO            UNIQUE_SNO
NAME           UNIQUE_NAME
ADDRESS        UNIQUE_ADDRESS

Now, we can drop these constraints by the same syntax, ALTER TABLE tablename DROP CONSTRAINT constraintName;

Note:- User-defined constraint name must be unique within a user else Oracle gives error:- ORA-02264: name already used by an existing constraint

SQL> CREATE TABLE test3 (
       sno number(10) 
       CONSTRAINT unique_sno UNIQUE
     );
ORA-02264: name already used by an existing constraint

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Also Learn,

• GROUP BY Clause
• Having Clause
• ORDER BY Clause

Search