SQL CHECK Constraint

SQL CHECK Constraint

The CHECK constraint in SQL is used to enforce specific rules for the values that a column can hold. It ensures that all rows in a table satisfy a defined condition, adding an extra layer of data integrity.

Key Points

1. Validation Rules:
   • Ensures that values inserted or updated in a column meet the specified condition.
2. Column-Level or Table-Level:
   • Can be applied to a single column or multiple columns in a table.
3. Customizable:
   • Allows defining complex conditions to meet business logic.
4. Database Enforced:
   • The database automatically checks and rejects invalid values.

Syntax

1. Add CHECK During Table Creation

Column-Level

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

Table-Level

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

2. Add CHECK to an Existing Table

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);

Examples

1. CHECK on a Single Column

Example: Age Restriction

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT CHECK (Age >= 18)
);

• This ensures that only employees aged 18 or above can be added.

Insert Example:

INSERT INTO Employees (EmployeeID, Name, Age)
VALUES (1, 'Alice', 25); -- Valid

INSERT INTO Employees (EmployeeID, Name, Age)
VALUES (2, 'Bob', 16); -- Error: Age must be >= 18

2. CHECK on Multiple Columns

Example: Salary Constraint

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

• This ensures that the salary is always greater than the bonus.

3. Add CHECK to an Existing Table

Adding a Constraint

ALTER TABLE Employees
ADD CONSTRAINT chk_age CHECK (Age BETWEEN 18 AND 65);

• Ensures that the age is between 18 and 65 for all employees.

Dropping the Constraint

ALTER TABLE Employees
DROP CONSTRAINT chk_age;

Common Use Cases

1. Range Validation:

   • Example: Restrict a column to specific ranges (Age >= 18 AND Age <= 65).

2. Conditional Logic:

   • Example: Ensuring dependent fields (Bonus <= Salary).

3. Value Matching:

   • Example: Limit values to a predefined set (Status IN ('Active', 'Inactive')).

Benefits of CHECK Constraint

1. Data Validation:

   • Prevents invalid data from being stored in the table.

2. Centralized Rules:

   • Enforces business logic at the database level, reducing reliance on application code.

3. Improved Data Integrity:

   • Ensures that all rows meet the specified conditions.

Limitations of CHECK Constraint

1. Cross-Table Validation:

   • Cannot reference columns in other tables (use triggers or foreign keys for such scenarios).

2. Database Support:

   • Some older database systems may have limited support for complex CHECK constraints.

3. Performance Impact:

   • Can affect performance for frequent inserts/updates, especially with complex conditions.

CHECK vs. Other Constraints

Feature	CHECK	NOT NULL	FOREIGN KEY	UNIQUE
Enforces Custom Rules	Yes	No	No	No
Column-Level and Table-Level	Yes	Yes	No	Yes
Ensures Referenced Data	No	No	Yes	No
Ensures Data Exists	No	Yes	Yes	No

Conclusion

The CHECK constraint is a powerful tool for enforcing custom validation rules directly in the database. It ensures data consistency, reduces errors, and centralizes business logic, making it easier to maintain a robust database.