SQL Constraints: Check Constraint

The check constraint is used in SQL to specify a condition that all values in a column must satisfy. If a value violates the condition, it is rejected by the SQL system.

Syntax

The syntax of a check constraint in SQL is as follows:

ALTER TABLE table_name 
ADD CONSTRAINT constraint_name 
CHECK (condition);

Example

Suppose we have a table named 'employees' with columns 'employee_id' and 'age'. We want to enforce a constraint that ensures all values in the 'age' column are between 18 and 65. The SQL query to add this check constraint would be:

ALTER TABLE employees
ADD CONSTRAINT age_check
CHECK (age >= 18 AND age <= 65);

Output

If the table already contains data that violates the check constraint, the SQL system will generate an error message and the check constraint will not be added. Otherwise, the constraint will be added and all future changes to the 'age' column must adhere to the specified condition.

Explanation

Check constraints restrict data entry into a table based on certain conditions. The check constraint ensures that the value inserted or updated in the column satisfies a specific condition, which is defined at the time of creating a table or adding a new column to an existing table.

When a value is inserted or updated in the respective column, the check constraint evaluates the expressions and returns a TRUE or FALSE result. If the evaluation result is TRUE, the execution of the insert or update statement is allowed. If the evaluation returns FALSE, then the respective operation is blocked.

Use

Check constraints are used to ensure data accuracy and consistency in a table. They are commonly used to restrict values that may be entered into a table column, such as ensuring that integer values are within a certain range or that string values match a specific pattern.

Check constraints can also be used to implement complex business rules, such as ensuring that the total value of an order does not exceed a certain amount or that an employee's seniority is greater than their salary.

Important Points

• Check constraints can be added to a table at the time of creation or later using the ALTER TABLE statement.
• It is important to ensure that the condition specified in the check constraint accurately reflects the business logic being enforced.
• Check constraints cannot reference other tables, and cannot contain subqueries or aggregate functions.

Summary

Check constraints are a powerful tool for enforcing data integrity in SQL tables. By restricting the values that can be entered into a column, check constraints ensure that each record in a table meets specific business logic requirements. Use check constraints to enforce data consistency and accuracy, and to implement complex business rules that govern your database operations.