SQLite § constraints

Constraints

A constraint is a declaration in the schema that values stored in the database must satisfy a stated condition. The engine enforces the constraint at insertion and update time, rejecting (or otherwise reacting to) attempts to violate it. Constraints are the principal mechanism by which a relational database enforces data integrity — the property that the data in the database faithfully represents the application’s domain. SQLite supports the standard constraint vocabulary of ISO SQL: NOT NULL, DEFAULT, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY, with several additions and subtractions documented below.

Column constraints versus table constraints

Constraints come in two syntactic forms. Column constraints attach to a single column and are written as part of that column’s definition. Table constraints may reference several columns and are written as separate clauses after the column list:

CREATE TABLE bouquet_flower (
    bouquet_id INTEGER NOT NULL,                          -- column constraint
    flower_id  INTEGER NOT NULL,                          -- column constraint
    quantity   INTEGER NOT NULL DEFAULT 1,                -- column constraints
    PRIMARY KEY (bouquet_id, flower_id),                  -- table constraint
    FOREIGN KEY (bouquet_id) REFERENCES bouquet(id),      -- table constraint
    FOREIGN KEY (flower_id)  REFERENCES flower(id)        -- table constraint
);

A constraint that references only a single column may be written either way. A composite primary key, a composite unique index, or a CHECK that references several columns can only be a table constraint.

Each constraint may be named with an optional CONSTRAINT clause, which assigns an identifier to the constraint that appears in error messages and in introspection output:

quantity INTEGER NOT NULL CONSTRAINT positive_quantity CHECK (quantity > 0)

Naming constraints is the recommended practice for constraints that are likely to fire at runtime — a meaningful constraint name in an error message is a substantial usability improvement.

NOT NULL

NOT NULL forbids storing the literal NULL in the column. Insertions and updates that violate the constraint are rejected.

CREATE TABLE customer (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    phone TEXT
);

INSERT INTO customer (name) VALUES (NULL);
-- Error: NOT NULL constraint failed: customer.name

The recommendation is to declare every column NOT NULL unless a meaning can be articulated for missing in that column. Loose null-permitting schemas are the source of a category of bug that strict-null discipline avoids; the cost of articulating null meaning at the schema level is recovered many times over in queries that need not handle NULL.

DEFAULT

DEFAULT provides a value that is used when an INSERT does not supply one for the column. The default expression is evaluated at the time of the insert.

created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))

A DEFAULT may be a literal (DEFAULT 0, DEFAULT 'unknown'), a special token (DEFAULT CURRENT_TIMESTAMP, DEFAULT CURRENT_DATE, DEFAULT CURRENT_TIME), or a parenthesised expression (DEFAULT (strftime(…))). The expression cannot reference other columns of the row being inserted.

A non-NOT NULL column without an explicit DEFAULT defaults to NULL. The conventional pattern for an auto-timestamped row is NOT NULL together with a date-valued DEFAULT:

CREATE TABLE event (
    id          INTEGER PRIMARY KEY,
    occurred_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
    payload     TEXT NOT NULL
);

INSERT INTO event (payload) VALUES ('user signed in');
SELECT id, occurred_at, payload FROM event;
-- 1|2026-05-08T14:01:23.456Z|user signed in

UNIQUE

UNIQUE enforces that no two rows have the same value in the constrained column or columns. Internally, UNIQUE is implemented as a unique index, and the engine consults the index on every insert and update.

CREATE TABLE customer (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE
);

INSERT INTO customer (email) VALUES ('ada@example.com');
INSERT INTO customer (email) VALUES ('ada@example.com');
-- Error: UNIQUE constraint failed: customer.email

A composite unique constraint is expressed as a table constraint:

CREATE TABLE bouquet_flower (
    bouquet_id INTEGER NOT NULL,
    flower_id  INTEGER NOT NULL,
    UNIQUE (bouquet_id, flower_id)
);

UNIQUE permits multiple NULLs by default, in accordance with ISO SQL — see NULL semantics. To forbid duplicate NULLs, use a partial unique index:

CREATE UNIQUE INDEX ix_customer_email
    ON customer (email)
    WHERE email IS NOT NULL;

A UNIQUE constraint can be declared with a collation, which controls the equality test:

email TEXT UNIQUE COLLATE NOCASE

The NOCASE collation makes Ada@Example.com and ada@example.com equal for the purposes of the constraint, which is the conventional behaviour for email addresses.

CHECK

CHECK enforces that an arbitrary boolean expression evaluates to true (or NULL) for every row. The expression may reference any column of the table.

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

A CHECK constraint is evaluated on every insert and every update of the row. Multi-column checks are expressed as table constraints:

CREATE TABLE event (
    id INTEGER PRIMARY KEY,
    started_at TEXT NOT NULL,
    ended_at   TEXT,
    CHECK (ended_at IS NULL OR ended_at >= started_at)
);

The expression may not reference other tables, and SELECT is not permitted within a CHECK. For checks that need to reference data in other tables, the conventional substitute is a TRIGGER (see Views and triggers).

CHECK constraints are an under-used SQL feature. Encoding domain invariants — a discount is a fraction between zero and one, an ISO date is well-formed, a status is one of a fixed enumeration — at the schema level prevents categories of bug that scattered application-side validation does not.

PRIMARY KEY

PRIMARY KEY declares the primary key of a table — the column or combination of columns that uniquely identifies each row. The constraint enforces both NOT NULL (in STRICT and WITHOUT ROWID tables) and UNIQUE.

A single-column primary key is most often declared inline:

CREATE TABLE customer (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

A composite primary key is declared as a table constraint:

CREATE TABLE bouquet_flower (
    bouquet_id INTEGER NOT NULL,
    flower_id  INTEGER NOT NULL,
    PRIMARY KEY (bouquet_id, flower_id)
);

In a conventional rowid table, INTEGER PRIMARY KEY is special: it aliases the rowid (see Tables and rowid) and is the recommended form for surrogate keys. Any other declaration of PRIMARY KEY creates an additional unique index and stores the key separately.

A historical wart preserved for backward compatibility: in a non-STRICT, conventional rowid table, an INTEGER PRIMARY KEY column may contain NULL, despite the constraint’s stated meaning. STRICT and WITHOUT ROWID tables both enforce non-NULL on the primary key, as does ISO SQL.

FOREIGN KEY

FOREIGN KEY declares that a column (or set of columns) in this child table references the primary key (or another unique key) of a parent table. The constraint enforces referential integrity: every non-NULL value of the foreign key must match an existing row in the parent table.

CREATE TABLE department (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE employee (
    id            INTEGER PRIMARY KEY,
    name          TEXT NOT NULL,
    department_id INTEGER REFERENCES department(id)
);

A composite foreign key is expressed as a table constraint:

FOREIGN KEY (a, b) REFERENCES parent(p, q)

FOREIGN KEY constraints are off by default in SQLite — a historical compatibility decision — and must be enabled per-connection:

PRAGMA foreign_keys = ON;

The recommendation is to enable foreign keys at the start of every connection, or to set the application-level default through the binding. Without foreign_keys = ON, the constraint is parsed and stored in the schema but is not enforced: the engine will permit orphan rows in the child table.

Referential actions

When a row in the parent table is updated or deleted, the engine consults the referential action declared on the foreign key. Five actions are accepted:

Action	Behaviour on delete or update of parent
`NO ACTION`	The default. The constraint is checked at the end of the statement; if any child row is left orphaned, the operation is rolled back.
`RESTRICT`	The constraint is checked immediately. The parent operation fails if any child row references it, even within a transaction.
`SET NULL`	The foreign-key column in every referencing child row is set to NULL.
`SET DEFAULT`	The foreign-key column is set to its declared `DEFAULT` value.
`CASCADE`	The corresponding child rows are deleted (on parent delete) or their foreign-key columns are updated to the new value (on parent update).

CREATE TABLE employee (
    id            INTEGER PRIMARY KEY,
    department_id INTEGER REFERENCES department(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

A foreign key without ON DELETE and ON UPDATE clauses defaults to NO ACTION — not RESTRICT. The distinction matters when the foreign-key check is deferred (within a transaction): NO ACTION permits intermediate states that violate the constraint as long as the constraint holds at the end of the statement, whereas RESTRICT fails on the first attempt.

The choice of action depends on the domain. For an employee.department_id, ON DELETE SET NULL says the department was abolished, the employees are now unassigned. For an order.customer_id, ON DELETE RESTRICT says a customer with orders cannot be deleted, the orders must be archived first. For an order_item.order_id, ON DELETE CASCADE says if the order is deleted, its items are too. Schemas should make these choices deliberately.

Deferred constraints

A foreign-key constraint is by default immediate: the check fires at the end of each statement. The constraint may be declared DEFERRABLE INITIALLY DEFERRED, in which case the check is postponed to the end of the enclosing transaction:

FOREIGN KEY (parent_id) REFERENCES parent(id) DEFERRABLE INITIALLY DEFERRED

Deferred constraints are useful for schema operations that temporarily introduce circular references or for bulk loads that establish referential integrity at the end. The non-deferred default is the conventional choice for application-level use.

AUTOINCREMENT

AUTOINCREMENT modifies an INTEGER PRIMARY KEY declaration to prevent reuse of rowids. Without AUTOINCREMENT, a deleted row’s rowid may be reissued to a subsequent row; with AUTOINCREMENT, the engine maintains a separate counter (in the table sqlite_sequence) that monotonically increases.

CREATE TABLE event (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    payload TEXT NOT NULL
);

AUTOINCREMENT carries a small overhead — the auxiliary counter must be read and updated on every insert — and is typically unnecessary. The engine’s default rowid assignment (one greater than the current maximum) is sufficient for most applications. The conventional reason to declare AUTOINCREMENT is when external systems (mobile clients, audit logs, cross-system references) hold rowids and must be insulated from rowid reuse. Without that requirement, the unmodified INTEGER PRIMARY KEY is preferred.

ON CONFLICT clauses

A constraint violation may be handled by one of five conflict resolution strategies, declared either on the constraint itself or on the offending statement. The strategies are:

Strategy	Behaviour
`ABORT`	The default. The current statement is rolled back, leaving the database unchanged; any prior changes in the transaction remain.
`ROLLBACK`	The entire transaction is rolled back.
`FAIL`	The current statement aborts but does not roll back changes already made by it.
`IGNORE`	The offending row is silently skipped.
`REPLACE`	The pre-existing row that caused the conflict is deleted, and the new row replaces it.

CREATE TABLE customer (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE ON CONFLICT REPLACE
);

INSERT INTO customer (email) VALUES ('ada@example.com');
INSERT INTO customer (email) VALUES ('ada@example.com');   -- old row replaced

The conflict strategy can also be specified on the statement:

INSERT OR IGNORE INTO customer (email) VALUES ('ada@example.com');
INSERT OR REPLACE INTO customer (email) VALUES ('ada@example.com');

The INSERT OR REPLACE form is the conventional UPSERT for SQLite predating version 3.24, when the proper UPSERT (ON CONFLICT … DO UPDATE) was added; the proper form is documented under INSERT, UPDATE, DELETE.

Constraint checking and PRAGMA

Three pragmas affect constraint enforcement:

PRAGMA foreign_keys = ON | OFF — enables or disables foreign-key enforcement for the connection. Off by default.
PRAGMA defer_foreign_keys = ON — temporarily defers foreign-key checks until the end of the current transaction; reverts at transaction end.
PRAGMA ignore_check_constraints = ON | OFF — disables CHECK constraint enforcement. Used during bulk loads when the data is known good and the constraint cost is significant; the recommendation is to leave it off in production.

The PRAGMA foreign_key_check and PRAGMA quick_check commands diagnose constraint violations across the database without modifying anything:

PRAGMA foreign_key_check;       -- reports orphaned child rows
PRAGMA quick_check;             -- reports general database corruption
PRAGMA integrity_check;         -- thorough check, slower

These are diagnostic commands, intended for periodic maintenance and post-import validation.

Designing a constraint set

The recommendations applicable to a typical application schema:

Declare every column NOT NULL unless a NULL meaning is articulable.
Use INTEGER PRIMARY KEY for surrogate primary keys (rowid alias).
Use STRICT tables for new code, with explicit INTEGER, REAL, TEXT, BLOB, or ANY types.
Use UNIQUE (with a collation, if the equivalence class is non-binary) for natural keys: emails, usernames, ISBN, social-security numbers.
Use CHECK constraints for domain restrictions: positive prices, percentage ranges, enumerated statuses (status IN ('pending', 'sent', 'delivered')), well-formed dates.
Use FOREIGN KEY for every cross-table reference, with explicit ON DELETE and ON UPDATE actions chosen per the domain.
Enable foreign keys with PRAGMA foreign_keys = ON at the start of every connection.
Name non-trivial constraints with CONSTRAINT name CHECK (…) for readable error messages.
Use INSERT OR IGNORE and the proper ON CONFLICT … DO UPDATE UPSERT for the duplicate-key cases that are part of normal operation.
Run PRAGMA foreign_key_check and PRAGMA integrity_check periodically — daily for production data — to detect drift.

A well-designed constraint set is the most powerful tool the schema offers for keeping the data in the database faithful to the application’s domain; the time spent at design is repaid many times over in queries that need not defensively check what the schema already guarantees.