SQL CHECK Constraint: Complete Guide

In SQL, the CHECK constraint is used to restrict the range of values in a column of a table. It can be used to validate the validity of specific conditions when inserting or updating data.

The CHECK constraint consists of the following components:

1. Column name: The name of the column specifying the constraints for the application.
2. Operators: Comparison operators used to define constraint conditions, such as equal to (=), less than (<), greater than (>).
3. Value: A constant value, expression, or subquery used for comparison with column values.
4. Constraint name (optional): Define a name for the constraint, which can be referenced when needing to delete or modify the constraint.

Here is an example of using a CHECK constraint when creating a table:

CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    age INT,
    salary DECIMAL(10, 2),
    CONSTRAINT chk_age CHECK (age >= 18)
);

In the example above, a table named employees has been created with columns for id, name, age, and salary. A CHECK constraint has been defined on the age column to require that the value must be greater than or equal to 18.

When inserting or updating data in the table, check if the constraints are met. If the constraints are not satisfied, an error will be returned and the insertion or update operation will be rejected.

Here is an example of a violation of a CHECK constraint when inserting data:

INSERT INTO employees (id, name, age, salary) VALUES (1, 'John', 16, 5000);

An error will be returned due to the inserted age value of 16 violating the conditions of the CHECK constraint.

You can modify the constraints of an existing table using CHECK constraints in the ALTER TABLE statement. For example, you can change the CHECK constraint of the ‘age’ column in the table to age >= 21 with the following statement.

ALTER TABLE employees
DROP CONSTRAINT chk_age,
ADD CONSTRAINT chk_age CHECK (age >= 21);

The detailed explanation and usage of CHECK constraints in SQL can be seen in the example above. It is an effective tool for restricting the range of values in a column, ensuring data integrity and consistency.