SQL CHECK Constraint

A SQL CHECK constraint is used to ensure that values in a column meet a specific condition or set of conditions. It helps enforce data integrity by restricting the range or type of data that can be entered into a table.

Syntax

CREATE TABLE table_name (
    column_name data_type CHECK (condition)
);

Example

Let's create a table called Employees where we want to enforce that the salary of each employee must be greater than 0.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50),
    Salary DECIMAL(10, 2) CHECK (Salary > 0)
);

Explanation

• EmployeeID is the primary key.
• Name is a column for the employee's name.
• Salary is a column for the employee's salary, which must be greater than 0 due to the CHECK constraint.

Inserting Data

If we try to insert valid and invalid data:

-- Valid insert
INSERT INTO Employees (EmployeeID, Name, Salary) 
VALUES (1, 'John Doe', 50000.00);

-- Invalid insert (will fail)
INSERT INTO Employees (EmployeeID, Name, Salary) 
VALUES (2, 'Jane Smith', -3000.00);

Output

For the valid insert, the data is added to the table:

For the invalid insert, you will receive an error message like:

ERROR: CHECK constraint failed: Employees

This error indicates that the Salary value does not satisfy the CHECK constraint condition.