Skip to content
Home » How to Add a Constraint and Remove a Constraint From a Relation?

How to Add a Constraint and Remove a Constraint 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
    Figure 1 – 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'));
    Figure 2 - Add Constraint EMPLOYEES table
    Figure 2 – Add Constraint EMPLOYEES table

    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.

    Constraint Name
    Figure 3 – 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;
    Figure 4 - Drop Constraint
    Figure 4 – Drop Constraint

    Summary

    Following the above four steps can help you to identify and remove the constraints set on your database tables. Note that these commands are performed on Oracle 10g, therefore, you must be careful while running the same on other database systems. The command and procedure may be different.