CHECK (SQL) Constraint

CHECK SQL constraint can be used to define a condition that data must be satisfied before data can be entered into a column. The condition can be defined on any column with any data type. CHECK SQL constraint can be used on a column along with other constraints. CHECK SQL constraint can be used to avoid NULL values but the best practice is to use NOT NULL constraint for that purpose. Most of the time CHECK SQL is used to define conditions on NUMBER or DATE type columns.

ADDING A CHECK  SQL CONSTRAINT
CHECK can be added using

1. Create Statement
2. Alter Statement

Adding CHECK SQL Constraint using CREATE:

Example:
Suppose you have business rule that says the value for salary column should never be a negative number. To achieve this purpose, following query will define CHECK SQL constraint on column salary of employee table using CREATE statement:

CREATE TABLE employee (full_name VARCHAR2(30), birth_date DATE, salary NUMBER CHECK (salary >= 0) );

Example 2:
The following query is same as one above except that it defines CHECK SQL constraint with name ck_salary

CREATE TABLE employee (full_name VARCHAR2(30), birth_date DATE, salary NUMBER CONSTRAINT ck _salary CHECK (salary >= 0) );

Adding CHECK Constraint using ALTER:

Example:
The following query will define CHECK SQL constraint on salary column of employee table given that there is no CHECK constraint existed earlier:

ALTER TABLE employee MODIFY (salary CHECK (salary >= 0));

Example 2:
The following query is same to one above except that it defines CHECK SQL constraint with name ck_salary

ALTER TABLE employee MODIFY (salary CONSTRAINT ck_salary CHECK (salary >= 0));

DROPPING A CHECK CONSTRAINT
CHECK SQL constraints can only be dropped using constraint name. So in case you have not defined the name for CHECK constraint, the system will itself define a name for constraint which can be obtained using following SQL:

SELECT  constraint_name FROM user_constraints WHERE constraint_type = ‘C’ AND UPPER(table_name) LIKE  'TABLE_NAME_IN_CAPITAL'

Example:
The following statement will get constraint name for CHECK SQL constraint which will then be used to drop CHECK constraint from department table:

SELECT  constraint_name FROM user_constraints WHERE constraint_type = ‘C’ AND UPPER(table_name) LIKE  'EMPLOYEE'

Result:
SYS_C003851507
This statement will drop CHECK SQL constraint using its name retrieved in previous query:

ALTER TABLE employee DROP CONSTRAINT SYS_C003851507;

Each table may have more than one CHECK constraint column. A CHECK constraint cannot be renamed.