CHECK Constraint in Oracle

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.

CHECK CONSTRAINT

Syntax

  • 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);
Parameters Explanation

column1, column2

  • Single or multiple columns to add in the table

logical_expression

  • Expressions to use in CHECK

constraint_name

  • User defined constraint name of CHECK

table_name

  • Single or multiple tables

condition

  • Condition to be used in CHECK constraint

datatype

  • Data type of columns in the table

Example

  • 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.

Please feel free to write us at connect@oraclemine.com or hioraclemine@gmail.com if you find mistakes or errors in any of the topics. Your suggestions to improve article helps us improve.

You can also Subscribe here to stay updated on the latest posts of OracleMine.com.

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 hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.