Check constraint controls the input of undesired data for each row in the table.
This constraint enforce data integrity by defining logical expression for a column or columns.
The true evaluation of expression validates the inserted or updated data. False evaluation prohibits the entry of data based on the condition mentioned in the check constraint.
It can be applied to the columns or group of columns of the same table only.
A table can have one or more than one CHECK constraints.
You cannot apply CHECK constraint to the views.
- Syntax of CHECK at column level
CREATE TABLE table_name ( …… column1 datatype CHECK (logical_expression), column2 datatype ……);
- Table level CHECK Syntax
CREATE TABLE table_name ( column1 datatype column2 datatype …… CONSTRAINT constraint_name CHECK(column1 condition) [DISABLE]);
- Alter statement level CHECK Syntax
ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK(column1 condition);
- Single or multiple columns to add in the table
- Expressions to use in CHECK
- User defined constraint name of CHECK
- Single or multiple tables
- Condition to be used in CHECK constraint
- Data type of columns in the table
- At the column level
CREATE TABLE employees ( empno NUMBER, empname VARCHAR2(200), deptno NUMBER CHECK (deptno =10) );
In this case, we will allow data with only department number 10. For data of other departments, an error will be returned during data insertion.
- At the table level
CREATE TABLE employees ( empno NUMBER, empname VARCHAR2(200), salary NUMBER, bonus NUMBER, CONSTRAINT salary_ck CHECK (salary > bonus + 10%) );
Here, the check constraint controls the entry of data using salary greater than bonus plus 10%.
- Using alter statement
ALTER TABLE employees ADD CONSTRAINT age_ck CHECK (age >= 18);
In this example, we allow data in employees table for personnel who are more than 18 years of age.
Hi I am Paras.
Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at email@example.com. Again I appreciate your visit. Hope to see you again and again!