How to use check constraint in Oracle

Introduction

Oracle Check constraint is used to enforce integrity rules based on logical expressions, such as comparisons. The logical expressions used should return the boolean value.
Example
A CHECK constraint on employee salaries so that no salary value is greater than 100000.
A CHECK constraint on department locations so that only the locations “DELHI”, “PATNA”, and “MUMBAI” are allowed.

How Oracle check constraint works

1. You define the check constraints on the column on a table. Check constraints is an logical expressions that evaluate to boolean value as true or false
2. When rows are inserted or updated in the table, oracle check the logical expressions.
3. If it is evaluated to True, oracle accepts the data and carries forward with insert or delete
4. If it evaluates to false, oracle rejects the data

Important points on check constraint

a. Check constraint can be created on a column or a group of columns.
b. A column may have one or more check constraints.
c. The order in which the constraints are evaluated is not defined
d. Never use CHECK constraints when any of the other types of integrity constraints can provide the necessary checking.
e. When defining multiple constraints, make sure they don’t conflict with each other

Check constraints Limitation

(1) The condition must be a boolean expression that can be evaluated using the values in the row being inserted or updated.
(2) The condition cannot have
a. subqueries or sequences.
b. SYSDATE, UID, USER, or USERENV SQL functions.
c. pseudo columns LEVEL, PRIOR, or ROWNUM.
d. user-defined SQL function.
(3) A check constraint cannot refer columns in other tables
(4) A check constraint cannot be created on an oracle view

How to create the check constraint in Oracle

Oracle check constraints can be created with the create table clause or after the table has been created with alter table clause.
Now with creating a table, check constraints can be created in in-line column syntax or out-of-line syntax.

in-line syntax

SQL> CREATE TABLE "EMP"
 ("EMPNO" NUMBER(4,0),
 "ENAME" VARCHAR2(10),
 "JOB" VARCHAR2(9),
 "MGR" NUMBER(4,0),
 "HIREDATE" DATE, 
 "SAL" NUMBER(7,2) check(SAL > 1000),
 "COMM" NUMBER(7,2),
 "DEPTNO" NUMBER(2,0),
  CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  );

 Table created.

We can give the check constraint a custom name also

SQL> CREATE TABLE "EMP"
    ("EMPNO" NUMBER(4,0),
     "ENAME" VARCHAR2(10),
     "JOB" VARCHAR2(9),
     "MGR" NUMBER(4,0),
     "HIREDATE" DATE,
     "SAL" NUMBER(7,2) constraint CHECK_SAL check(SAL > 1000),
     "COMM" NUMBER(7,2),
     "DEPTNO" NUMBER(2,0),
      CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
      );
 
Table created.

out-of-line syntax

SQL> CREATE TABLE "EMP"
 ("EMPNO" NUMBER(4,0),
 "ENAME" VARCHAR2(10),
 "JOB" VARCHAR2(9),
 "MGR" NUMBER(4,0),
 "HIREDATE" DATE,
 "SAL" NUMBER(7,2) ,
 "COMM" NUMBER(7,2),
 "DEPTNO" NUMBER(2,0),
 CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO"),
 check(SAL > 1000)
 );

 Table created.

We can give the check constraint a custom name also

SQL> CREATE TABLE "EMP"
   ("EMPNO" NUMBER(4,0),
   "ENAME" VARCHAR2(10), 
   "JOB" VARCHAR2(9),  
   "MGR" NUMBER(4,0),
   "HIREDATE" DATE,
   "SAL" NUMBER(7,2) ,
   "COMM" NUMBER(7,2),
   "DEPTNO" NUMBER(2,0),
   CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO"),
   constraint CHECK_SAL check(SAL > 1000)
    );
 
Table created.

When multiple columns are involved, we go with out-of-line syntax only
If you use, inline syntax, it will throw an error

SQL> CREATE TABLE "EMP"
    ("EMPNO" NUMBER(4,0),
    "ENAME" VARCHAR2(10),
    "JOB" VARCHAR2(9),
    "MGR" NUMBER(4,0),
    "HIREDATE" DATE,
    "SAL" NUMBER(7,2) constraint CHECK_SAL check(SAL > 1000 and 
     DEPTNO is not null),
    "COMM" NUMBER(7,2),
    "DEPTNO" NUMBER(2,0),
    CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
    );
   
"SAL" NUMBER(7,2) constraint CHECK_SAL check(SAL > 1000 and DEPTNO is not null),
                                                                                  *
 ERROR at line 7:
 ORA-02438: Column check constraint cannot reference other columns

 SQL> CREATE TABLE "EMP"
     ("EMPNO" NUMBER(4,0),
      "ENAME" VARCHAR2(10),
      "JOB" VARCHAR2(9),
      "MGR" NUMBER(4,0),
      "HIREDATE" DATE,
      "SAL" NUMBER(7,2) ,
      "COMM" NUMBER(7,2),
      "DEPTNO" NUMBER(2,0),
      CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO"),
      constraint CHECK_SAL check(SAL > 1000 and DEPTNO is not null)
       );
 Table created.

Now check constraint can be added after the table is created using the alter table syntax

SQL> CREATE TABLE "EMP"
    ("EMPNO" NUMBER(4,0),
    "ENAME" VARCHAR2(10),
    "JOB" VARCHAR2(9),
    "MGR" NUMBER(4,0),  
    "HIREDATE" DATE,
    "SAL" NUMBER(7,2) ,
    "COMM" NUMBER(7,2),
    "DEPTNO" NUMBER(2,0),  
    CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
    );
 Table created.

 SQL> alter table "EMP" add constraint  CHECK_SAL 
     check(SAL > 1000 and   DEPTNO is not null);
 Table altered.

How to find the check constraint on the table

Following dictionary, views contain the information on constraint
ALL_CONSTRAINTS
ALL_CONS_COLUMNS
USER_CONSTRAINTS
USER_CONS_COLUMNS
DBA_CONSTRAINTS
DBA_CONS_COLUMNS

SQL> column CONSTRAINT_NAME format a20
SQL> column SEARCH_CONDITION format a50
SQL> SELECT Constraint_name, Search_condition  
     FROM User_constraints  
     WHERE Table_name = 'EMP'  AND  
     Constraint_type = 'C';

Here C stands for Check constraint

How to enable/disable the check constraint

SQL> alter table "EMP"  disable constraint CHECK_SAL;
 Table altered.
 SQL> alter table "EMP"  enable  constraint CHECK_SAL;
 Table altered.

How to drop the check constraint

 alter table "EMP"  drop constraint CHECK_SAL;
 Table altered. 

Example Of Check constraints

col1 >= 0 AND col1 < 85857 user_override in ('Y','N') class = 0 OR class = 1 hwm > 0
cm = 0 OR cm = 1
SEQ >= 0
status = 0 OR status = 1

Also Reads
alter table add primary key oracle : primary key in oracle uniquely identify the row in the table. It cannot be null & can be created at the time of table creation or after the table is created
not null constraint in Oracle : not null constraint in oracle is used to enforce not null values in the column in the table. Check out how to add, drop the null constraints
Unique Key in Oracle: Unique key enforces unique in the column in the table and helps us identify the row quickly. Oracle creates the unique index for the key if no index is available
drop foreign key constraint oracle: We can drop the constraint in oracle using alter table command. we can drop primary, foreign key, check,not null and unique constraint with the same command
delete command in oracle
https://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm

Recommended Courses

Here is the nice Udemy Course for Oracle SQL
Oracle-Sql-Step-by-step : This course covers basic sql, joins, Creating Tables and modifying its structure, Create View, Union, Union -all and much other stuff. A great course and must-have course for SQL starter
The Complete Oracle SQL Certification Course : This is a good course for anybody who wants to be Job ready for SQL developer skills. A nice explained course
Oracle SQL Developer: Essentials, Tips and Tricks : Oracle Sql developer tool is being used by many developers. This course gives us tricks and lessons on how to effectively use it and become a productive sql developer
Oracle SQL Performance Tuning Masterclass 2020 : Performance tuning is one of the critical and most sought skills. This is a good course to learn about it and start doing sql performance tuning