arvb.net

SQL check constraints

2 min read

SQL CHECK constraints enforce data integrity by ensuring that values in a column (or a set of columns) meet specified conditions. They can be defined inline with a column or as a table-level constraint, and they prevent invalid data from being inserted or updated in a table.

Examples

Example 1: Inline CHECK Constraint

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    birthdate DATE CHECK (birthday > '1900-01-01')
    salary DECIMAL(10,2) NOT NULL CHECK (salary > 0),
    department VARCHAR(50)
    hire_date DATE CHECK (hire_date > birthdate)
);

Explanation:

  • The inline CHECK constraint on salary ensures that only values greater than 0 are allowed.

Example 2: Table-Level CHECK Constraint

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    quantity INTEGER NOT NULL,
    CONSTRAINT chk_price_quantity CHECK (price > 0 AND quantity >= 0)
);

Explanation:

  • The table-level CHECK constraint chk_price_quantity ensures that price is greater than 0 and quantity is non-negative. This allows you to enforce conditions that span multiple columns.

Final Notes

  • Data Integrity: CHECK constraints are crucial for enforcing business rules at the database level.
  • Inline vs. Table-Level: Use inline constraints for single-column validations and table-level constraints for multi-column rules.
  • Testing: Always test your constraints to ensure they handle all expected data scenarios correctly.

Graph View