Check Constraints for Business Validation

Use check constraints to enforce domain-specific logic, such as range checks or formatted pattern matching.

In this lesson, you will learn how to use CHECK constraints to enforce specific business rules directly at the database level, ensuring your data always remains valid.

The Role of Business Validation

While data types (like INTEGER or VARCHAR) define the technical format of your data, they rarely capture the full nuance of your business logic. For instance, a database might accept a "discount percentage" as any integer, but your business rule might dictate that a discount must be between 0 and 100. Instead of relying on your application code to catch these errors every time, you can instruct the database to reject invalid entries automatically using a CHECK constraint.

Applying Constraints to Ranges and Patterns

A CHECK constraint is a rule attached to a column that evaluates a logical expression for every row. If the expression returns TRUE, the data is saved. If it returns FALSE, the database rejects the transaction, maintaining your data integrity.

Consider an Employees table where we want to ensure that a salary is always positive and that a department_code follows a specific format:

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    salary DECIMAL(10, 2),
    department_code VARCHAR(3),
    
    -- Ensuring salary is always positive
    CONSTRAINT chk_positive_salary CHECK (salary > 0),
    
    -- Ensuring department code follows a specific pattern (e.g., starts with 'D')
    CONSTRAINT chk_dept_format CHECK (department_code LIKE 'D__')
);

In this example, the database engine becomes a "gatekeeper." If a user tries to insert a salary of -500 or a department code of HR1, the database will throw an error and refuse the operation.

Why Use Constraints?

By embedding validation within the schema, you create a "single source of truth." No matter which application, script, or user interacts with the database, the business rules remain active. This is far more robust than relying on front-end validation alone, as it protects your data from being corrupted by batch imports or accidental manual entries.

Key Takeaway: Use CHECK constraints to transform your business rules into immutable database laws, preventing invalid data from ever reaching your tables.