19 total views
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.
Contents
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));
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;