Oracle Check Constraint

CHECK Constraint is a Constraint that is used to set the range of a numeric column according to the condition. To clearly, understand this concept, let's consider a case wherein we have details of Students who have provided participation in different sports. Now, you want to create a group for only those students, whose participation did not exceed more than 2 sports. However, a CHECK Constraint is created with a range less than 3. Now, anyone would not be able to add details for the student with sports participation of more than 2.

Check Constraint Syntax in Oracle

CONSTRAINT constraint_name CHECK(Condition...);

Oracle Check Constraint Example

Let's create a table named Table1 and store in it the name of Students along with the no. of sports the students are involved in. However, we want to restrict the number of participants using CHECK CONSTRAINT to less than 3. Follow the code to perform the same task:

create table Table1
(Name Varchar(20), sports_Part number (20)
Constraint my_check CHECK(sports_Part<3));

Output

Now, inserting the values into the sports_Part column will not allow entering numeric values more than 2.

TRYING TO INPUT 1 and 2

TRYING TO INPUT 3 OR MORE THAN 3

How to Drop Check Constraint in Oracle

We can also Drop CHECK Constraint using the following syntax:

Syntax

Alter table Table_Name
Drop constraint constraint_name;

Example

You need to drop my_check Constraint of table Table1. Follow the step to do the same:

Alter table Table1
Drop constraint my_check;