Check Constraint

A check constraint is a type of constraint that is used in SQL to ensure that a specified condition is met before data is added or modified in a table. Check constraints are used to enforce business rules or logic on a table column or a set of columns.

A check constraint is defined as part of the table schema, and it is created using the ALTER TABLE statement.

Syntax

The syntax for creating a check constraint is as follows:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
CHECK (condition);

The table_name parameter specifies the name of the table on which the constraint is being applied. The constraint_name parameter is a user-defined name for the constraint, and the condition parameter is the logical expression that defines the constraint.

Example 1

For example, let’s consider a table named Employees that has a column named Age. We can add a check constraint to ensure that the age of the employees is greater than or equal to 18, using the following SQL statement:

ALTER TABLE Employees
ADD CONSTRAINT chk_Employees_Age CHECK (Age >= 18);

Once the check constraint is added, any attempt to insert or update a row in the Employees table with an age less than 18 will result in an error.

Example 2

CREATE TABLE Training_Course
(
ID INT PRIMARY KEY,
NAME VARCHAR(250) NOT NULL,
DURATION INT,
PRICE INT
);

ALTER TABLE Training_Course 
ADD CONSTRAINT CHK_TC 
CHECK (DURATION > 4 and PRICE < 500 );

INSERT INTO Training_Course(ID, NAME, DURATION, PRICE) 
values(1,'SQL',5,200);
(1 row(s) affected)

INSERT INTO Training_Course(ID, NAME, DURATION, PRICE) 
values(2,'T-SQL',7,700);
The INSERT statement conflicted with the CHECK constraint "CHK_TC". 
The conflict occurred in database "model", table "dbo.Training_Course".

Check constraints are useful in maintaining data integrity in a database. They ensure that the data stored in a table is consistent with the business rules or logic of the organization. By using check constraints, developers can prevent the insertion of invalid or inconsistent data into a table, which helps to maintain the quality of the data and reduce the risk of data errors.