CHECK constraint is used for specifying condition which must be true for all rows.
While adding a CHECK constraint to a column we create a logical expression which either returns true or false. All the values we try to insert in the column after applying CHECK constraint must satisfy the condition in expression.
Some Points to keep in Mind:
- We cannot include subquery in a CHECK constraint.
- We cannot add CHECK constraint on a SQL view.
We will learn how to add, drop and enable/disable a CHECK constraint in this tutorial and learn its usage.
Add CHECK constraint – Using a CREATE TABLE statement
The syntax for adding a check constraint to a column using CREATE TABLE statement in Oracle is as follows:
CREATE TABLE table_name ( column1 data_type, column2 data_type, ... CONSTRAINT constraint_name CHECK (c_column_name condition) [DISABLE] );
Here,
table_name – It is the name of table in which we want to add CHECK constraint.
column1, column2 – These are the columns that we will creating with table.
constraint_name – Here, we give a unique name to our check constraint. It’s advisable to give a descriptive name to this constraint so that its easier to identify if Oracle returns an error when we violate this constraint.
DISABLE – Optional. Adding this keyword at the end will create the constraint but it won’t be enforced by Oracle.
Example
Let’s try to add check constraint to Teachers table to ensure that the teachers salary is within a specific range only:
CREATE TABLE teachers ( teacher_id NUMBER(10), teacher_name VARCHAR2(50), salary NUMBER(10), age NUMBER(5) CONSTRAINT check_salary CHECK (salary BETWEEN 3000 and 6000) );
Adding this constraint to Salary column won’t allow us to enter any value in salary column that does not lie within the range of 3000 to 6000.
Let’s add one more CHECK constraint to the same table:
CREATE TABLE teachers ( teacher_id NUMBER(10), teacher_name VARCHAR2(50), salary NUMBER(10), age NUMBER(5) CONSTRAINT check_age CHECK (age > 21) );
check_age constraint added in this example ensures that the age entered is always above 21.
Add CHECK constraint -Using an ALTER TABLE statement
The syntax for adding a check constraint in an already existing table using ALTER TABLE statement is as follows:
Syntax:
ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (c_column_name condition) [DISABLE];
Here,
table_name – Name of table in which we want to add CHECK constraint.
column1, column2 – Columns that we will be creating with table.
constraint_name – Name of our check constraint.
DISABLE – Optional. Adding this keyword at the end will create the constraint but it won’t enforce it.
Example
Let’s create a query which ensures that salary column accepts only 3 values which denote the three tiers of salary that teachers get based on their seniority.
ALTER TABLE teachers
ADD CONSTRAINT check_teachers_salary
CHECK (salary IN ('3000', '4500', '6000'));The Salary column will now only accept one of the 3 values that we gave in the brackets.
Drop a Check Constraint
The syntax for dropping a check constraint is as follows:
Syntax:
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
Example:
Let’s drop the check constraint that we added in Teachers table:
ALTER TABLE teachers DROP CONSTRAINT check_teachers_salary;
This query drops the check_teachers_salary constraint from teachers table.
Enable/Disable a Check Constraint
We can enable or disable a Check constraint using the following syntax:
ALTER TABLE table_name [ENABLE/DISABLE] CONSTRAINT constraint_name;
Example:
Let’s a disable CHECK constraint in Teachers table using ALTER TABLE statement.
ALTER TABLE teachers DISABLE CONSTRAINT check_teachers_salary;
The check constraint check_teachers_salary in Teachers table has been disable and we can re-enable it by using ENABLE keyword instead of DISABLE in above query.
In this article, we learned about the usage of CHECK constraint and how it can be added, dropped and enabled or disabled in an Oracle Table.