Check Constraints

CHECK constraints let you encode business rules directly in schema.

That means bad values are rejected early, before they spread into application logic.

Core Concepts

flowchart TD
  A[New row value] --> B[Evaluate CHECK expression]
  B -->|True or NULL| C[Accept value]
  B -->|False| D[Reject change]

Constraint idea	Example
Non-negative numeric	CHECK (price >= 0)
Range validation	CHECK (age BETWEEN 0 AND 120)
Allowed set	CHECK (status IN ('new','done'))

Code Examples

-- Add validation directly in table definition.
CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  price      REAL NOT NULL CHECK (price >= 0),
  stock      INTEGER DEFAULT 0 CHECK (stock >= 0)
);

Expected output
Table created with CHECK constraints on price and stock.

-- Review stored CREATE statement including CHECK clauses.
SELECT sql
FROM sqlite_schema
WHERE type = 'table' AND name = 'products';

sql
CREATE TABLE products (... CHECK (price >= 0) ... CHECK (stock >= 0) ...)

SQLite-Specific Nuances

SQLite Nuance

In SQLite, a CHECK passes when expression result is true (non-zero) or NULL.

Use NOT NULL together with CHECK when nulls should be rejected.

Common Pitfalls / Best Practices

Pitfall

Writing complex CHECK expressions that are hard to read and maintain.

Hard-to-read constraints become hidden bugs later.

Best Practice

Prefer small, explicit checks (price >= 0, quantity >= 0) and compose multiple constraints when needed.

Quick Challenge

Create a reviews table where rating must be between 1 and 5.

View Solution

CREATE TABLE reviews (
  review_id INTEGER PRIMARY KEY,
  rating    INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment   TEXT
);

Expected output
Table is created with bounded rating validation.