SQL CHECK Constraint

 The CHECK constraint in SQL is used to limit the values that can be inserted into a column. It ensures that the data entered into a column meets a specific condition or a set of conditions. When a CHECK constraint is defined on a column, only values that satisfy the condition(s) will be allowed to be inserted or updated in the table.

Syntax

CREATE TABLE table_name (
    column_name datatype,
    CONSTRAINT constraint_name CHECK (condition)
);

Example of a CHECK constraint

Here’s an example of how to use the CHECK constraint to ensure that the values in an age column are greater than 18:

CREATE TABLE users (
    user_id INT,
    user_name VARCHAR(100),
    age INT,
    CONSTRAINT check_age CHECK (age > 18)
);

In this case, the CHECK constraint ensures that only records with an age greater than 18 can be inserted into the users table.

Example of CHECK constraint with multiple conditions:

You can combine multiple conditions in a CHECK constraint, for example, ensuring that a salary is both positive and below a certain value:

CREATE TABLE employees (
    employee_id INT,
    employee_name VARCHAR(100),
    salary DECIMAL(10, 2),
    CONSTRAINT check_salary CHECK (salary > 0 AND salary < 200000)
);

Adding CHECK constraint to an existing table:

You can also add a CHECK constraint to an existing table using the ALTER TABLE statement.

ALTER TABLE employees
ADD CONSTRAINT check_salary CHECK (salary > 0 AND salary < 200000);

Notes:

• The condition in a CHECK constraint must return a Boolean value (TRUE or FALSE).
• A CHECK constraint can be defined on multiple columns, but each condition must reference valid columns in the table.
• The constraint will enforce validation during INSERT or UPDATE operations.

If you try to insert data that violates the CHECK constraint, an error will occur. For example:

INSERT INTO users (user_id, user_name, age) 
VALUES (1, 'John Doe', 15);

This would trigger an error since the age is less than 18, violating the CHECK constraint.