SQLite CHECK Constraints: Validate Data at the Table Level

A CHECK Constraint Is a Rule Every Row Must Satisfy

A CHECK constraint is a boolean expression you attach to a table. SQLite evaluates it on every INSERT and UPDATE, and if the expression comes back false, the operation fails. It's a way to bake a business rule — "price can't be negative," "status has to be one of these three values" — straight into the schema.

CREATE TABLE products (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL NOT NULL CHECK (price >= 0)
);

INSERT INTO products (name, price) VALUES ('Notebook', 4.50);
INSERT INTO products (name, price) VALUES ('Free sample', 0);
INSERT INTO products (name, price) VALUES ('Bug', -1);

SELECT * FROM products;

The first two rows go in. The third raises CHECK constraint failed and is rejected — the table never sees it. The constraint enforces the rule for every writer, whether that's your app, a migration script, or someone poking around in the CLI.

Column-Level vs Table-Level

You can write a CHECK in two places. After a column definition (column-level) or after all the columns (table-level). They behave the same; the difference is what reads naturally.

-- Column-level: rule about one column.
CREATE TABLE accounts (
    id      INTEGER PRIMARY KEY,
    balance REAL CHECK (balance >= 0)
);

-- Table-level: rule that touches multiple columns.
CREATE TABLE bookings (
    id         INTEGER PRIMARY KEY,
    start_date TEXT NOT NULL,
    end_date   TEXT NOT NULL,
    CHECK (start_date <= end_date)
);

INSERT INTO bookings (start_date, end_date) VALUES ('2026-01-10', '2026-01-15');
INSERT INTO bookings (start_date, end_date) VALUES ('2026-02-01', '2026-01-31');

The first booking inserts. The second fails — the end is before the start. Single-column rules read better as column-level; anything that compares two or more columns reads better as table-level.

Restricting Values to a List

A common use is forcing a column to one of a fixed set of values. SQLite has no native enum type, so CHECK ... IN (...) is the idiom:

CREATE TABLE tickets (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    status TEXT NOT NULL CHECK (status IN ('open', 'in_progress', 'closed'))
);

INSERT INTO tickets (title, status) VALUES ('Login broken', 'open');
INSERT INTO tickets (title, status) VALUES ('Typo in footer', 'closed');
INSERT INTO tickets (title, status) VALUES ('Mystery', 'pending');

The third row fails — 'pending' isn't in the allowed list. If you ever need to add a new status, you'll have to rebuild the table (more on that below), so think a little before locking the list down. But for genuinely fixed vocabularies like role names or order states, this is exactly the constraint you want.

Naming Your Constraints

By default, a constraint is anonymous. The error message just says "CHECK constraint failed" with the expression, which is fine when there's one CHECK on the table — and confusing when there are five. Add a name with CONSTRAINT:

CREATE TABLE employees (
    id       INTEGER PRIMARY KEY,
    name     TEXT NOT NULL,
    salary   REAL NOT NULL,
    bonus    REAL NOT NULL DEFAULT 0,
    CONSTRAINT salary_positive CHECK (salary > 0),
    CONSTRAINT bonus_not_negative CHECK (bonus >= 0),
    CONSTRAINT bonus_not_huge CHECK (bonus <= salary)
);

INSERT INTO employees (name, salary, bonus) VALUES ('Ada', 60000, 70000);

Now the failure message includes the constraint name, so you know immediately which rule was violated. Naming costs a few extra characters and pays for itself the first time something breaks in production.

CHECK and NULL: The Gotcha

CHECK passes when the expression is true or NULL. It only fails on an explicit false. That sounds odd until you remember that almost any comparison with NULL evaluates to NULL, not to true or false.

CREATE TABLE people (
    id  INTEGER PRIMARY KEY,
    age INTEGER CHECK (age >= 0)
);

INSERT INTO people (age) VALUES (25);
INSERT INTO people (age) VALUES (NULL);
INSERT INTO people (age) VALUES (-5);

SELECT * FROM people;

The NULL row goes in cleanly — NULL >= 0 is NULL, not false, so the CHECK doesn't fail. If you actually want to forbid both negative numbers and missing values, combine NOT NULL with the CHECK:

CREATE TABLE people (
    id  INTEGER PRIMARY KEY,
    age INTEGER NOT NULL CHECK (age >= 0)
);

INSERT INTO people (age) VALUES (NULL);

Now the insert fails on the NOT NULL constraint before the CHECK even runs. The two constraints work together: NOT NULL covers absence, CHECK covers shape.

Useful Built-In Functions Inside CHECK

The expression can use most of SQLite's built-in functions. A few that come up a lot:

CREATE TABLE users (
    id       INTEGER PRIMARY KEY,
    email    TEXT NOT NULL CHECK (email LIKE '%_@_%.__%'),
    username TEXT NOT NULL CHECK (length(username) BETWEEN 3 AND 20),
    country  TEXT NOT NULL CHECK (country = upper(country))
);

INSERT INTO users (email, username, country) VALUES ('ada@example.com', 'ada', 'US');
INSERT INTO users (email, username, country) VALUES ('not-an-email', 'ada2', 'US');
INSERT INTO users (email, username, country) VALUES ('boris@x.io', 'b', 'US');
INSERT INTO users (email, username, country) VALUES ('cara@x.io', 'cara', 'us');

Three failures: a bad email shape, a too-short username, and a lowercase country code. LIKE handles simple patterns; length(), upper(), lower(), and arithmetic are all fair game. Just keep the expression deterministic — using something like random() or current_timestamp inside CHECK creates rules that can flip between rows, which is rarely what you want.

CHECK vs Trigger

CHECK and triggers can both reject bad data, and beginners often wonder which to reach for. The rule of thumb:

• CHECK when the rule depends only on the row being written. "This column compared to that column," "this value within a range," "this string matches a pattern."
• Trigger (specifically a BEFORE INSERT/UPDATE trigger that calls RAISE) when the rule depends on other rows, other tables, or needs to do something more complicated than a single boolean expression.

CHECK is faster, simpler, and visible in the schema — anyone reading CREATE TABLE sees the rule. Reach for a trigger only when CHECK can't express what you need.

You Can't Drop a CHECK With ALTER

This is the one rough edge. SQLite has no ALTER TABLE ... DROP CONSTRAINT. To remove or change a CHECK, you rebuild the table:

BEGIN;

CREATE TABLE products_new (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL NOT NULL CHECK (price >= 0 AND price <= 1000000)
);

INSERT INTO products_new SELECT * FROM products;
DROP TABLE products;
ALTER TABLE products_new RENAME TO products;

COMMIT;

Wrap the whole thing in a transaction so a failure midway leaves the database alone. If other tables have foreign keys pointing at the table you're rebuilding, the dance gets longer — disable foreign_keys, rebuild, re-enable, recheck. We'll cover that in the migrations doc later in the curriculum.

Next: UNIQUE Constraints

CHECK validates the shape of values inside a row. The next constraint, UNIQUE, validates relationships across rows — guaranteeing no two rows share the same value in a column or set of columns. That's coming up next.