In this post , I am going add a check constraint and delete it. So it is a general procedure that you follow to add or remove a constraint from a Relation .
In this step we 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 EMPLOYEE|
Here we add the constraint to the table .
ALTER TABLE EMPLOYEE ADD CONSTRAINT CHK CHECK (GENDER IN( ‘M’,’F’));
Now, I am going to delete the constraint, but before I drop the constraint , let me 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’;
Now that I have the name of the constraint , We can delete the constraint using following command.
ALTER TABLE EMPLOYEE DROP CONSTRAINT CHK;