CHECK Constraint
The CHECK constraint is used to restrict the values that can be stored in a column. It allows data to be inserted only when a specified condition is satisfied.
What is a CHECK Constraint?
A CHECK constraint defines a condition that must be true when data is inserted or updated in a table.
If the condition is false, SQL Server rejects the INSERT or UPDATE operation.
Simple Example
If the Age column must contain a value greater than or equal to 18, a CHECK constraint can be used to enforce this rule.
Why Use CHECK Constraint?
- Restricts invalid data.
- Maintains data integrity.
- Applies business rules at database level.
- Prevents incorrect values from being inserted.
- Validates data during INSERT and UPDATE operations.
Create CHECK Constraint
A CHECK constraint can be created while defining a table.
CREATE TABLE Students
(
StudentId INT PRIMARY KEY,
StudentName VARCHAR(100),
Age INT CHECK (Age >= 18)
);
Insert Valid Data
Since the CHECK condition is Age >= 18, values greater than or equal to 18 are accepted.
INSERT INTO Students
VALUES (101, 'Rahul', 22);
Insert Invalid Data
A value that does not satisfy the CHECK condition will be rejected.
INSERT INTO Students
VALUES (102, 'Priya', 15);
Named CHECK Constraint
A CHECK constraint can be given a meaningful name.
CREATE TABLE Students
(
StudentId INT PRIMARY KEY,
StudentName VARCHAR(100),
Age INT,
CONSTRAINT CK_Students_Age
CHECK (Age >= 18)
);
Add CHECK Constraint to Existing Table
A CHECK constraint can also be added to an existing table using ALTER TABLE.
ALTER TABLE Students
ADD CONSTRAINT CK_Students_Age
CHECK (Age >= 18);
CHECK with Multiple Conditions
A CHECK constraint can contain more than one condition.
CREATE TABLE Employees
(
EmployeeId INT PRIMARY KEY,
EmployeeName VARCHAR(100),
Age INT,
Salary DECIMAL(10,2),
CONSTRAINT CK_Employees_Age
CHECK (Age >= 18),
CONSTRAINT CK_Employees_Salary
CHECK (Salary > 0)
);
CHECK with Range
CHECK can be used to restrict a value within a specific range.
CREATE TABLE Students
(
StudentId INT PRIMARY KEY,
StudentName VARCHAR(100),
Marks INT,
CONSTRAINT CK_Students_Marks
CHECK (Marks BETWEEN 0 AND 100)
);
Example Output
| StudentId | StudentName | Age | Marks |
|---|---|---|---|
| 101 | Rahul | 22 | 85 |
| 102 | Priya | 21 | 92 |
The records above satisfy the defined CHECK conditions.
CHECK Constraint Structure
Insert / Update Data
|
↓
CHECK Condition
|
┌───┴───┐
↓ ↓
TRUE FALSE
↓ ↓
Accepted Rejected
↓ ↓
Table Error
Remove CHECK Constraint
A named CHECK constraint can be removed using ALTER TABLE and DROP CONSTRAINT.
ALTER TABLE Students
DROP CONSTRAINT CK_Students_Age;
Important Points
- CHECK validates values against a condition.
- Invalid values are rejected.
- Multiple CHECK constraints can exist in a table.
- CHECK constraints can be named.
- CHECK constraints can be added or removed using ALTER TABLE.
Summary
In this section, we learned how the CHECK constraint validates data, how to create and name CHECK constraints, how to add them to existing tables and how invalid values are prevented.