SQL

CHECK Constraint in SQL

✅ The CHECK constraint in SQL is used to restrict the values that can be stored in a column or a combination of columns. It ensures that every inserted or updated value satisfies a specified logical condition, helping maintain data accuracy and business rules directly within the database.

📖 What is the CHECK Constraint?

A CHECK constraint evaluates a Boolean expression whenever data is inserted or updated. If the condition evaluates to TRUE, the operation succeeds. If it evaluates to FALSE, the database rejects the operation.

Information

The CHECK constraint is useful for enforcing business rules such as age limits, valid salary ranges, positive quantities, or allowed status values.

🎯 Why Use CHECK?

The CHECK constraint helps prevent invalid or inconsistent data from entering the database.

📌 Enforce business rules.
📌 Prevent invalid data entry.
📌 Improve data integrity.
📌 Reduce application-level validation.
📌 Ensure consistent data across applications.

📝 Basic Syntax

CHECK Constraint Syntax

CREATE TABLE table_name
(
    column_name data_type
    CHECK (condition)
);

💡 Create a Table with CHECK

Create a Students table where the student's age must be at least 18 years.

CHECK Constraint Example

CREATE TABLE Students
(
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT CHECK (Age >= 18)
);

The database accepts only rows where Age is 18 or greater.

✅ Valid INSERT

This statement succeeds because the age satisfies the constraint.

Valid CHECK Example

INSERT INTO Students
(StudentID, Name, Age)
VALUES
(101, 'Alice', 20);

❌ Invalid INSERT

This statement fails because the age does not satisfy the CHECK condition.

Invalid CHECK Example

INSERT INTO Students
(StudentID, Name, Age)
VALUES
(102, 'Bob', 16);

Error

The database rejects the insert because the value does not satisfy the CHECK (Age >= 18) constraint.

🔗 CHECK on Multiple Columns

A CHECK constraint can validate conditions involving multiple columns.

Table-Level CHECK Constraint

CREATE TABLE Employees
(
    EmployeeID INT PRIMARY KEY,
    Salary DECIMAL(10,2),
    Bonus DECIMAL(10,2),

    CHECK (Bonus <= Salary)
);

This rule ensures that an employee's bonus never exceeds their salary.

➕ Add a CHECK Constraint to an Existing Table

You can add a CHECK constraint after a table has already been created.

Add CHECK Constraint

ALTER TABLE Students
ADD CONSTRAINT CHK_Students_Age
CHECK (Age >= 18);

➖ Remove a CHECK Constraint

Removing a CHECK constraint uses database-specific syntax.

Drop CHECK Constraint (SQL Server Example)

ALTER TABLE Students
DROP CONSTRAINT CHK_Students_Age;

Important

The syntax for dropping a CHECK constraint varies across database systems. Some databases require different commands or support different features.

📊 Common CHECK Constraint Examples

Constraint	Purpose
CHECK (Age >= 18)	Allow only adult students.
CHECK (Salary > 0)	Ensure positive salaries.
CHECK (Quantity >= 0)	Prevent negative inventory.
CHECK (Marks BETWEEN 0 AND 100)	Limit marks to a valid range.
CHECK (Status IN ('Active', 'Inactive'))	Restrict allowed status values.

💼 Real-World Example

An online shopping system requires product prices to always be greater than zero.

Products Table

CREATE TABLE Products
(
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10,2),

    CHECK (Price > 0)
);

This prevents products from being stored with zero or negative prices.

⚖️ CHECK vs NOT NULL vs UNIQUE

Feature	CHECK	NOT NULL	UNIQUE
Validates Values	✅ Yes	❌ No	❌ No
Disallows NULL	❌ No	✅ Yes	Depends on the database.
Prevents Duplicates	❌ No	❌ No	✅ Yes
Main Purpose	Enforce business rules.	Require a value.	Ensure uniqueness.

⚠️ Common Mistakes

❌ Writing conditions that conflict with valid business requirements.
❌ Assuming every SQL database supports identical CHECK behavior.
❌ Adding a CHECK constraint when existing data violates the rule.
❌ Using overly complex expressions that reduce readability.

Warning

Before adding a CHECK constraint to an existing table, ensure that all current rows satisfy the new condition. Otherwise, the operation may fail.

⚠️ Best Practices

Best Practice

Use CHECK constraints to enforce simple and meaningful business rules, assign descriptive names to constraints, keep validation expressions easy to understand, validate existing data before adding new constraints, and complement CHECK with other constraints such as NOT NULL, UNIQUE, and PRIMARY KEY when appropriate.

🚀 Key Points to Remember

📌 CHECK validates data before it is stored.
📌 It enforces business rules at the database level.
📌 It can validate one column or multiple columns together.
📌 Invalid INSERT and UPDATE operations are rejected.
📌 Multiple CHECK constraints can exist in a table.
📌 Constraint syntax and capabilities may vary slightly across database systems.

>>"A CHECK constraint protects your database by ensuring only valid data can enter the table."

Summary

✅ The CHECK constraint is a powerful SQL feature for enforcing business rules and maintaining data integrity. By validating values during data modification, it helps keep databases accurate, consistent, and reliable while reducing the need for repetitive validation in application code.