SQL CHECK Constraint

What is a CHECK Constraint?

A CHECK constraint is used to limit the values that can be entered into a column. It ensures that all values in a column satisfy a specific condition, providing a way to enforce data integrity at the column level.

Syntax of CHECK Constraint

The basic syntax for defining a CHECK constraint is as follows:

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

Example of Using CHECK Constraint

Here’s an example of creating a table with a CHECK constraint:

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  Name VARCHAR(100),
  Age INT,
  Salary DECIMAL(10, 2),
  CONSTRAINT chk_age CHECK (Age >= 18),
  CONSTRAINT chk_salary CHECK (Salary > 0)
);

This ensures that the age of an employee must be 18 or older and that the salary must be a positive value.

Multiple CHECK Constraints

You can define multiple CHECK constraints on a table to enforce different rules:

CREATE TABLE Products (
  ProductID INT PRIMARY KEY,
  ProductName VARCHAR(100),
  Price DECIMAL(10, 2),
  Quantity INT,
  CONSTRAINT chk_price CHECK (Price >= 0),
  CONSTRAINT chk_quantity CHECK (Quantity >= 0)
);

This ensures that both the price and quantity of products are non-negative.

Important Considerations

When using the CHECK constraint, keep in mind:

• The condition can use any valid SQL expression.
• CHECK constraints can reference only columns in the same table.
• If a CHECK constraint is violated during an insert or update operation, the operation fails.
• It is advisable to carefully plan CHECK constraints to avoid unnecessary constraints that can complicate data entry.