SQL CHECK Constraint Tutorial

Example Table

We will use the following table named employees for our examples:

    CREATE TABLE employees (
        employee_id INT PRIMARY KEY,
        name VARCHAR(50),
        position VARCHAR(50),
        salary DECIMAL(10, 2),
        age INT,
        CHECK (salary > 0),
        CHECK (age >= 18)
    );

    INSERT INTO employees (employee_id, name, position, salary, age) VALUES
    (1, 'John Doe', 'Manager', 60000.00, 45),
    (2, 'Jane Smith', 'Developer', 55000.00, 30),
    (3, 'Emily Johnson', 'Designer', 50000.00, 25);
    

Employees Table

employee_id	name	position	salary	age
1	John Doe	Manager	60000.00	45
2	Jane Smith	Developer	55000.00	30
3	Emily Johnson	Designer	50000.00	25

Using the CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a single column, it allows only certain values for this column.

Example: Adding a CHECK Constraint

To add a CHECK constraint to ensure that the salary is greater than 0 and age is at least 18:

    CREATE TABLE employees (
        employee_id INT PRIMARY KEY,
        name VARCHAR(50),
        position VARCHAR(50),
        salary DECIMAL(10, 2),
        age INT,
        CHECK (salary > 0),
        CHECK (age >= 18)
    );
    

Result:

Command	CREATE TABLE employees (..., CHECK (salary > 0), CHECK (age >= 18));
Result	The table employees is created with CHECK constraints on salary and age.

Example: Inserting Data with CHECK Constraint

To insert data into the employees table:

    INSERT INTO employees (employee_id, name, position, salary, age) VALUES
    (1, 'John Doe', 'Manager', 60000.00, 45),
    (2, 'Jane Smith', 'Developer', 55000.00, 30),
    (3, 'Emily Johnson', 'Designer', 50000.00, 25);
    

Result:

Command	INSERT INTO employees (employee_id, name, position, salary, age) VALUES (...);
Result	Data is inserted successfully as all values meet the CHECK constraints.

Example: Violating a CHECK Constraint

To insert data that violates the CHECK constraint:

    INSERT INTO employees (employee_id, name, position, salary, age) VALUES
    (4, 'Alice Brown', 'Intern', -1000.00, 17);
    

Result:

Command	INSERT INTO employees (employee_id, name, position, salary, age) VALUES (4, 'Alice Brown', 'Intern', -1000.00, 17);
Result	Error: The insert fails because the salary is not greater than 0 and age is not at least 18.