How to add and drop constraints from existing table in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

How to add and drop constraints from an existing table in Oracle

In this article, I am going to discuss How to add and drop constraints from an existing table in Oracle with Examples. Please read our previous article where we discussed How to Create user-defined Constraint names in Oracle with Examples.

How to add constraints to an existing table in oracle?

Suppose, you have created one table and later on you want to add constraints to that existing table. To do so, we need to use the following syntax.

ALTER TABLE <TN> ADD CONSTRAINT <CONSTRAINT KEY NAME> <CONSTRAINT TYPE> (<COLUMN NAME>);

Example:

Please create the Department and Employee tables without any constraints by executing the following SQL Script.

CREATE TABLE Department1
( 
    Id   INT, 
    Name VARCHAR2(20), 
    Location  VARCHAR2(20) 
);

CREATE TABLE Employee1 
( 
    ID    INT, 
    Name VARCHAR2(20), 
    Email VARCHAR2(20),
    AGE INT, 
    DepartmentId INT
); 

As you can see, the above two tables are created without constraints. Now, let us see how to add different types of constraints in the above two tables.

ADDING PRIMARY KEY CONSTRAINT IN ORACLE:

Let us add the primary key on the Id column of both the Department1 and Employee1 table. Note, the constraint should be unique.

ALTER TABLE Department1 ADD CONSTRAINT PK_ID2 PRIMARY KEY(Id);
ALTER TABLE Employee1 ADD CONSTRAINT PK_ID3 PRIMARY KEY(Id);

When you execute the above ALTER TABLE statement, then you will see that the tables are altered as shown in the below image. Here, it adds the Primary Key Constraint on the Id column of both the tables.

ADDING UNIQUE CONSTRAINTS IN ORACLE:

Let us add the unique key constraint on the Email column of the Employee1 table. To do so, we need to execute the following query.

ALTER TABLE Employee1 ADD CONSTRAINT UQ_Email UNIQUE(Email);

When you execute the above ALTER TABLE statement, then you will see that the table is altered as shown in the below image. Here, it adds the Unique Key Constraint on the Email column of the Employee1 table.

ADDING CHECK CONSTRAINT IN ORACLE:

Let us add the Check constraint on the Age column of the Employee1 table. To do so, we need to execute the following query.

ALTER TABLE Employee1 ADD CONSTRAINT CHK_AGE CHECK(Age>= 18);

When you execute the above ALTER TABLE statement, then you will see that the table is altered as shown in the below image. Here, it adds the Check Constraint on the Age column of the Employee1 table.

ADDING “NOT NULL” CONSTRAINT IN ORACLE:

We need to use the following syntax to add a NOT NULL constraint on the existing table in oracle.

ALTER TABLE <TN> MODIFY <COLUMN NAME> CONSTRAINT <CONSTRAINT KEY NAME> NOT NULL;

Let us add the NOT NULL constraint on the Name column of the Employee1 table. To do so, we need to execute the following query.

ALTER TABLE Employee1 MODIFY Name CONSTRAINT NN_NAME NOT NULL;

When you execute the above ALTER TABLE statement, then you will see that the table is altered as shown in the below image. Here, it adds the NOT NULL Constraint on the Name column of the Employee1 table.

ADDING FOREIGN KEY CONSTRAINTS IN ORACLE:

We need to use the following syntax to add a FOREIGN KEY constraint on the existing table in oracle.

Syntax: ALTER TABLE <TN> ADD CONSTRAINT <CONSTRAINT KEY NAME> FOREIGN KEY(<COMMON COLUMN OF CHILD TABLE>) REFERENCES <PARENT TABLE>(<COMMON COLUMN OF PARENT TABLE>) ON DELETE CASCADE / ON DELETE SET NULL;

Let us add the FOREIGN KEY constraint on the DepartmentId column of the Employee1 table which references to the Id column of the Department1 table. To do so, we need to execute the following query.

ALTER TABLE Employee1 ADD CONSTRAINT FK_DEPARTMENTID FOREIGN KEY(DepartmentId) REFERENCES Department1(Id) ON DELETE CASCADE;

When you execute the above ALTER TABLE statement, then you will see that the table is altered as shown in the below image. Here, it adds the FOREIGN KEY Constraint on the DepartmentId column of the Employee1 table which refers to the Id column of the Department1 table.

We have added different types of constraints in the Employee1 table. Let us verify constraints in the Employee1 table by executing the below query.

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='EMPLOYEE1';

Once you execute the above query, you will get the following output. By default, NOT NULL will create a check constraint in oracle. Here, R means Foreign Key Constraint, C means Check Constraint, U means Unique constraint and P means Primary Key Constraints.

How to drop constraints from an existing table in Oracle?

We need to use the following syntax to drop constraints from an existing table in oracle.

ALTER TABLE <TN> DROP CONSTRAINT <CONSTRAINT KEY NAME>;

DROPPING PRIMARY KEY:

Let us drop the primary key from the Employee1 table. To do so, we need to execute the following query. In the employee table, the primary key constraint is created with the name PK_ID3.

ALTER TABLE Employee1 DROP CONSTRAINT PK_ID3;

Once you execute the above query, the primary key constraint will be deleted from the Employee1 table.

DROPPING FOREIGN KEY:

Let us drop the foreign key from the Employee1 table. To do so, we need to execute the following query. In the Employee1 table, the foreign key constraint is created with the name FK_DEPARTMENTID.

ALTER TABLE Employee1 DROP CONSTRAINT FK_DEPARTMENTID;

Once you execute the above query, the foreign key constraint will be deleted from the Employee1 table.

Note: When we drop the primary key along with foreign key constraints from parent and child tables then we need to use the “CASCADE” statement as shown in the below example.

ALTER TABLE Department1 DROP CONSTRAINT PK_ID2 CASCADE;

DROPPING UNIQUE, CHECK, NOT NULL CONSTRAINTS:

Let us drop the UNIQUE, CHECK, NOT NULL constraints from the Employee1 table by executing the following SQL Scripts.

ALTER TABLE Employee1 DROP CONSTRAINT CHK_AGE;
ALTER TABLE Employee1 DROP CONSTRAINT UQ_EMAIL;
ALTER TABLE Employee1 DROP CONSTRAINT NN_NAME;

In the next article, I am going to discuss How to Rename a constraint in Oracle with Examples. Here, in this article, I try to explain How to add and drop constraints from an existing table in Oracle with Examples and I hope you enjoy this How to add and drop constraints from an existing table in Oracle with Examples article.