What is a CHECK Constraint in SQL?

A CHECK constraint in SQL is a rule used to restrict the values that can be inserted or updated in a column.

In simple words:

CHECK constraint ensures that only valid data is stored in a table.

Why CHECK Constraints are Important

Databases store critical business data such as:

Employee salaries
Product prices
Student marks
Customer ages
Order quantities

Without validation:

Invalid data may enter database
Reports become incorrect
Business rules may break

CHECK Constraints Solve These Problems

By:

Enforcing data validation at database level

Simple Real-Life Example

Think about:

An employee management system

Business Rule

Salary should never be negative.

Invalid Data

salary = -5000

Solution

Use CHECK constraint

CHECK Constraint Internal Architecture

Insert / Update Operation
          |
          v
CHECK Condition Evaluated
          |
    +-----+------+
    |            |
 Valid        Invalid
    |            |
    v            v
Data Stored   Error Returned

Main Purpose of CHECK Constraint

Validate data
Enforce business rules
Improve data quality
Prevent invalid entries

Basic CHECK Constraint Syntax

CREATE TABLE employees (

    employee_id INT PRIMARY KEY,

    salary DECIMAL(10,2)

    CHECK (salary > 0)

);

Meaning

Salary must be greater than 0

Valid Insert Example

INSERT INTO employees

VALUES (1, 50000);

Result

Insert successful

Invalid Insert Example

INSERT INTO employees

VALUES (2, -1000);

Result

ERROR:
CHECK constraint violated

CHECK Constraint Query Flow

Insert or Update Request
          |
          v
CHECK Condition Evaluated
          |
    +-----+------+
    |            |
TRUE         FALSE
    |            |
    v            v
Success      Error

Common CHECK Constraint Examples

Age Validation

CHECK (age >= 18)

Marks Validation

CHECK (marks BETWEEN 0 AND 100)

Gender Validation

CHECK (gender IN ('Male', 'Female'))

Quantity Validation

CHECK (quantity > 0)

Status Validation

CHECK (status IN ('ACTIVE', 'INACTIVE'))

Column-Level CHECK Constraint

Defined directly on a column.

Example

salary DECIMAL(10,2)

CHECK (salary > 0)

Table-Level CHECK Constraint

Defined at table level.

Example

CREATE TABLE employees (

    employee_id INT,

    salary DECIMAL(10,2),

    bonus DECIMAL(10,2),

    CHECK (salary > bonus)

);

Meaning

Salary must always be greater than bonus

Named CHECK Constraint

Constraints can be named for easier maintenance.

Example

CREATE TABLE employees (

    employee_id INT,

    age INT,

    CONSTRAINT chk_age

    CHECK (age >= 18)

);

Benefits of Naming Constraints

Easier debugging
Better maintenance
Clearer error identification

CHECK Constraint vs NOT NULL

Feature	CHECK Constraint	NOT NULL
Purpose	Validate condition	Prevent NULL values
Logic Support	Yes	No
Example	salary > 0	name NOT NULL

CHECK Constraint vs UNIQUE

Feature	CHECK Constraint	UNIQUE
Purpose	Validate rules	Prevent duplicates
Business Logic	Yes	Limited

CHECK Constraint in Banking Systems

Banking systems use CHECK constraints for:

Account balance validation
Transaction amount limits
Interest rate validation
Customer age restrictions

Example

CHECK (balance >= 0)

CHECK Constraint in E-Commerce

E-commerce systems use CHECK constraints for:

Product price validation
Stock quantity validation
Discount percentage limits
Order quantity restrictions

Example

CHECK (discount BETWEEN 0 AND 100)

CHECK Constraint in Learning Platforms

Learning systems use CHECK constraints for:

Marks validation
Attendance percentage validation
Course duration limits
Student age restrictions

CHECK Constraint in Microservices

Microservices architectures use CHECK constraints for:

Service-level validation
Data integrity enforcement
Business rule consistency

Advantages of CHECK Constraint

Improves data quality
Enforces business rules
Reduces invalid data
Database-level validation

Disadvantages of CHECK Constraint

Complex conditions may impact performance
Business rule changes require schema updates
Different DB support variations

Performance Considerations

CHECK constraints may:

Increase validation overhead during inserts/updates

Optimization Techniques

Keep conditions simple
Avoid unnecessary complex expressions
Use proper indexing where needed

Adding CHECK Constraint to Existing Table

ALTER TABLE employees

ADD CONSTRAINT chk_salary

CHECK (salary > 0);

Removing CHECK Constraint

ALTER TABLE employees

DROP CONSTRAINT chk_salary;

CHECK Constraint in JPA/Hibernate

Hibernate supports CHECK constraints using:

@Check annotation

Example

@Check(

    constraints = "salary > 0"

)

Best Practices

Use CHECK constraints for business rules
Keep conditions simple and readable
Name constraints clearly
Validate critical fields at DB level
Combine with application-level validation

Common Interview Mistake

Many developers think:

CHECK constraint only validates numeric values

Reality

CHECK constraints can validate:

Numeric ranges
Text values
Dates
Business conditions

Professional Interview Answer

A CHECK constraint in SQL is a database constraint used to enforce custom validation rules on column values during INSERT and UPDATE operations. It ensures that only valid data satisfying a specified condition can be stored in the database. CHECK constraints are commonly used for enforcing business rules such as salary greater than zero, marks within valid ranges, valid status values, quantity restrictions, and age validation. They improve data quality, maintain integrity, and reduce invalid entries by performing validation directly at the database level. Enterprise systems such as banking platforms, e-commerce applications, ERP systems, learning management systems, and microservices architectures heavily use CHECK constraints to enforce critical business rules and ensure consistent data validation across applications.

Why Interviewers Like This Answer

Clearly explains validation purpose
Includes real business rule examples
Mentions INSERT and UPDATE validation
Discusses enterprise-level usage
Shows strong database integrity understanding

Frequently Asked Questions