CHECK Constraint in SQL

The CHECK constraint is used to ensure that values in a column satisfy a specified condition.

CREATE TABLE students (
    age INT CHECK (age >= 18)
);

Here, the age must be 18 or greater.

CHECK is useful when you want to prevent invalid data from being stored.

For example:

• Age should be at least 18.
• Marks should be between 0 and 100.
• Salary should be greater than 0.
• Status should contain an allowed value.

The basic syntax is:

column_name data_type CHECK (condition)

Example:

age INT CHECK (age >= 18)

You can define a CHECK constraint while creating a table.

CREATE TABLE students (
    student_id INT,
    age INT CHECK (age >= 18)
);

The database checks the age condition when data is inserted or updated.

If the value satisfies the CHECK condition, the record can be inserted.

CREATE TABLE students (
    age INT CHECK (age >= 18)
);

INSERT INTO students (age)
VALUES (25);

25 satisfies the condition age >= 18.

If the value does not satisfy the condition, the database rejects the operation.

INSERT INTO students (age)
VALUES (15);

This violates CHECK (age >= 18).

CHECK is commonly used with numeric columns.

CREATE TABLE products (
    price DECIMAL(10,2) CHECK (price > 0)
);

The price must be greater than zero.

You can use CHECK to restrict marks between 0 and 100.

CREATE TABLE results (
    student_name VARCHAR(100),
    marks INT CHECK (marks >= 0 AND marks <= 100)
);

Values such as 50 are valid, while 120 are invalid.

Salary can be restricted to positive values.

CREATE TABLE employees (
    employee_id INT,
    salary DECIMAL(10,2) CHECK (salary > 0)
);

A salary of 0 or a negative salary would violate the condition.

You can restrict age to a specific range.

CREATE TABLE students (
    age INT CHECK (age >= 5 AND age <= 60)
);

The age must be between 5 and 60.

CHECK can also be used to restrict text values.

CREATE TABLE students (
    status VARCHAR(20)
    CHECK (status IN ('Active', 'Inactive'))
);

Only the specified status values are allowed by this condition.

A CHECK condition can restrict a column to specific values.

CREATE TABLE students (
    gender VARCHAR(10)
    CHECK (gender IN ('Male', 'Female', 'Other'))
);

Other values violate the condition.

Multiple conditions can be combined using logical operators.

CREATE TABLE students (
    age INT CHECK (age >= 18 AND age <= 60)
);

Both conditions must be satisfied.

The OR operator can be used when more than one condition is acceptable.

CREATE TABLE employees (
    department VARCHAR(30)
    CHECK (
        department = 'IT'
        OR department = 'HR'
    )
);

The department must satisfy at least one of the conditions.

You can give a CHECK constraint a specific name.

CREATE TABLE students (
    age INT,
    CONSTRAINT chk_student_age CHECK (age >= 18)
);

The constraint is named chk_student_age.

A table can have multiple CHECK constraints.

CREATE TABLE students (
    age INT CHECK (age >= 18),
    marks INT CHECK (marks >= 0 AND marks <= 100)
);

Each column has its own validation rule.

The CHECK condition is evaluated when a row is inserted.

CREATE TABLE products (
    price DECIMAL(10,2)
    CHECK (price > 0)
);

INSERT INTO products (price)
VALUES (500);

The value 500 satisfies the condition.

CHECK is also important when existing records are updated.

UPDATE products
SET price = -100
WHERE product_id = 1;

If the table has CHECK (price > 0), this update violates the constraint.

CHECK and NOT NULL can be used together.

CREATE TABLE students (
    age INT NOT NULL CHECK (age >= 18)
);

NOT NULL requires a value, while CHECK validates that value.

CHECK can also be combined with DEFAULT.

CREATE TABLE products (
    quantity INT DEFAULT 0
    CHECK (quantity >= 0)
);

The default quantity is 0, and negative quantities are not allowed by the CHECK condition.

CHECK and PRIMARY KEY have different purposes.

• PRIMARY KEY uniquely identifies a record.
• CHECK validates whether a value satisfies a condition.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    age INT CHECK (age >= 18)
);

UNIQUE and CHECK solve different problems.

• UNIQUE prevents duplicate values.
• CHECK restricts values according to a condition.

CREATE TABLE students (
    email VARCHAR(150) UNIQUE,
    age INT CHECK (age >= 18)
);

You can add a CHECK constraint to an existing table.

ALTER TABLE students
ADD CONSTRAINT chk_age
CHECK (age >= 18);

The existing data should be compatible with the new constraint.

A named CHECK constraint can be removed using ALTER TABLE in systems that support dropping named constraints.

ALTER TABLE students
DROP CHECK chk_age;

The exact syntax can vary between database systems.

CHECK can use BETWEEN to define a range.

CREATE TABLE students (
    marks INT CHECK (marks BETWEEN 0 AND 100)
);

Marks must be within the specified range.

CHECK can use IN to allow a predefined set of values.

CREATE TABLE employees (
    status VARCHAR(20)
    CHECK (status IN ('Active', 'Inactive', 'On Leave'))
);

The status must match one of the allowed values.

A common mistake is defining a condition that does not match the actual business requirement.

CHECK (marks >= 50)

This would reject all marks below 50. If the requirement is to allow marks from 0 to 100, the condition should be designed accordingly.

CHECK constraints help maintain data integrity by preventing values that violate defined rules.

For example, a fee should not normally be negative.

fee DECIMAL(10,2) CHECK (fee >= 0)

Let's create a student table with multiple CHECK constraints.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT CHECK (age >= 5 AND age <= 60),
    marks INT CHECK (marks >= 0 AND marks <= 100),
    status VARCHAR(20)
    CHECK (status IN ('Active', 'Inactive'))
);

This table validates age, marks, and status.

Here is a complete example using several constraints together.

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL CHECK (age >= 18),
    marks INT CHECK (marks >= 0 AND marks <= 100),
    status VARCHAR(20) NOT NULL DEFAULT 'Active'
        CHECK (status IN ('Active', 'Inactive'))
);

INSERT INTO students
(name, age, marks, status)
VALUES
('Rahul', 22, 85, 'Active'),
('Priya', 20, 92, 'Active');

The table uses PRIMARY KEY, NOT NULL, DEFAULT, and CHECK constraints to help maintain valid student data.