How to Add a Constraint and Remove a Constraint From a Relation?

Constraints are like rules on 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'));
Add Constraint
Add Constraint

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 necessary step if you are not the DBA who created the constraint on the relation 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 exist. In this example, EMPLOYEES.

Constraint Name
Constraint Name

STEP4:

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

ALTER TABLE EMPLOYEE DROP CONSTRAINT CHK;

 

Drop Constraint
Drop Constraint
Advertisements