Other related articles:

Recently viewed articles:

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

Create Statement

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:

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.