How to Add Constraints and Remove Constraints From a Relation?

Constraints are like rules in relation so that you cannot violate them. There are plenty of constraint types that we use. In this article, we will add a check constraint and delete it from a relation.

The general process to add or remove a constraint from a Relation is as follows.

STEP 1:

In this step, you create EMPLOYEE relation and put a check constraint on Gender.

CREATE TABLE EMPLOYEE (EMPNO NUMBER(5) PRIMARY KEY, ENAME VARCHAR2(20), GENDER CHAR(2), SALARY NUMBER(7,2));

Create Table EMPLOYEES - DESC EMPLOYEES — Create Table EMPLOYEES – DESC EMPLOYEES

STEP 2:

Here we add the constraint to the table .

ALTER TABLE EMPLOYEE ADD CONSTRAINT CHK CHECK (GENDER IN( 'M','F'));

STEP 3:

Now, you are going to delete the constraint, but before you drop the constraint, let us show you, how to get the constraint name first.

It is a necessary step if you are not the DBA who created the constraint on the relationship and you cannot delete the constraint unless you have appropriate permission.

SELECT  CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'you table name';

In the above command, you only need to change the “Your Table Name’ with a table name that exists. In this example, EMPLOYEES.

STEP4:

Now that I have the name of the constraint , We can delete the constraint using following command.

ALTER TABLE EMPLOYEE DROP CONSTRAINT CHK;