SQL — Check

What is CHECK?

A CHECK constraint validates that a value meets a condition before allowing it to be stored. If the condition is false, the insert or update is rejected.

Basic Syntax

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10, 2) CHECK (price > 0)
);

Named CHECK Constraint

Give it a name for easier management:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  age INT,
  salary DECIMAL(10, 2),
  CONSTRAINT chk_age CHECK (age >= 18),
  CONSTRAINT chk_salary CHECK (salary > 0)
);

Multiple Conditions

CREATE TABLE orders (
  id INT PRIMARY KEY,
  quantity INT,
  total DECIMAL(10, 2),
  CONSTRAINT chk_quantity CHECK (quantity > 0 AND quantity <= 1000),
  CONSTRAINT chk_total CHECK (total >= 0)
);

CHECK with Dates

CREATE TABLE events (
  id INT PRIMARY KEY,
  start_date DATE,
  end_date DATE,
  CONSTRAINT chk_dates CHECK (end_date >= start_date)
);

CHECK with Strings

CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(50),
  email VARCHAR(100),
  CONSTRAINT chk_username CHECK (LENGTH(username) >= 3),
  CONSTRAINT chk_email CHECK (email LIKE '%@%.%')
);

Adding CHECK to Existing Table

ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary >= 0);

Dropping a CHECK Constraint

ALTER TABLE employees
DROP CONSTRAINT chk_salary;

CHECK vs Application Validation

Database-level CHECK constraints protect data regardless of which application writes to it:

-- Even if a bug in your app sends salary = -500,
-- the database rejects it:
INSERT INTO employees (name, salary) VALUES ('Test', -500);
-- ERROR: CHECK constraint failed

MySQL Limitation

MySQL 5.7 ignores CHECK constraints (syntax is accepted but not enforced). MySQL 8.0.16+ enforces them. MariaDB 10.2.1+ supports them.

Practical Example

CREATE TABLE inventory (
  id INT PRIMARY KEY AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  sku VARCHAR(20) NOT NULL UNIQUE,
  quantity INT NOT NULL DEFAULT 0,
  unit_price DECIMAL(10, 2) NOT NULL,
  warehouse VARCHAR(50),
  CONSTRAINT chk_qty CHECK (quantity >= 0),
  CONSTRAINT chk_price CHECK (unit_price > 0),
  CONSTRAINT chk_warehouse CHECK (warehouse IN ('Main', 'East', 'West'))
);

Best Practices

• Use meaningful constraint names (chk_price_positive not sys_c001234)
• Add CHECK constraints to enforce business rules at the database level
• Don't rely solely on application validation — databases enforce rules for all clients
• Document complex conditions with comments

Practice

1. Create a students table with a CHECK that age is between 16 and 100
2. Add a CHECK constraint to ensure email contains @
3. Create a bookings table where end date must be after start date
4. Add a CHECK to an existing table using ALTER TABLE