SQL CHECK Constraint

The CHECK constraint ensures that a column's values meet specific conditions before being inserted or updated. It is used to enforce data integrity by restricting values based on a condition.

 

Key Features of SQL CHECK Constraint

• Enforces custom conditions on column values.
• Can be applied to single or multiple columns.
• Prevents insertion or update of invalid data.
• Works with various operators like >, <, =, BETWEEN, IN, etc.

 

SQL CHECK Constraint Syntax

Defining CHECK in Table Creation

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Age INT CHECK (Age >= 18)
);

• Ensures that the Age column contains values 18 or higher.

CHECK with Multiple Conditions

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Price DECIMAL(10,2),
    Quantity INT,
    CHECK (Price > 0 AND Quantity >= 1)
);

• Ensures Price is greater than 0 and Quantity is at least 1.

 

Adding a CHECK Constraint to an Existing Table

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

• Adds a CHECK constraint on Age.

 

Removing a CHECK Constraint

ALTER TABLE Employees  
DROP CONSTRAINT CHK_Age;

 

Inserting Data into a Table with CHECK Constraint

INSERT INTO Employees (EmployeeID, Age)  
VALUES (1, 25);  -- ✅ Successful

INSERT INTO Employees (EmployeeID, Age)  
VALUES (2, 16);  -- ❌ Error: Age must be 18 or higher 

 

Summary

• The CHECK constraint enforces conditions on column values.
• Can be applied to one or multiple columns.
• Prevents insertion of invalid data.
• Can be added or removed using ALTER TABLE.