- DDL Commands
- Basic SQL Queries
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));
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;
- DDL Commands
- Basic SQL Queries
The post How to Add a Constraint and Remove a Constraint From a Relation? appeared first on Notesformsc.