NOT NULL, UNIQUE, PRIMARY KEY, CHECK, and DEFAULT

Learning outcomes

In the prerequisite modeling course, constraints represented facts such as “a device code must be unique” or “severity must stay within a valid range.” SQLite turns those facts into executable rules. This lesson treats constraints as part of the database’s state contract: if a row would make the database invalid, SQLite should reject the write before that state becomes durable.

Constraints describe states the database refuses to store

A constraint is more useful than an application-side if statement because every writer that goes through SQLite—CLI, migration, script, test, or application connection—meets the same rule. Application validation still matters for friendly error messages and richer business logic, but durable invariants belong as close to the data as SQLite can express them.

NOT NULL: absence is not valid for this column

NOT NULL is a column constraint in SQLite. It means an INSERT or UPDATE may not leave that column as SQL NULL. An empty string, zero, and the word unknown are not NULL, so use CHECK or application validation if those values are also invalid.

DROP TABLE IF EXISTS constraint_note;CREATE TABLE constraint_note (    note_id     INTEGER PRIMARY KEY,    summary     TEXT NOT NULL,    severity    INTEGER NOT NULL DEFAULT 1);INSERT INTO constraint_note(summary) VALUES ('Inspect seal');SELECT note_id, summary, severity FROM constraint_note;-- Deliberately invalid:INSERT INTO constraint_note(summary) VALUES (NULL);

The first insert succeeds and uses the default severity of 1. The second fails with a NOT NULL constraint error. The row is rejected by the SQLite engine itself; the application does not need to remember a separate validation rule just to protect the file.

UNIQUE and PRIMARY KEY: identity and alternate keys

A UNIQUE constraint says that no two rows may have the same non-NULL key combination. A table can have many UNIQUE constraints but only one PRIMARY KEY. For multi-column rules, a table-level declaration is usually clearer.

DROP TABLE IF EXISTS device_rule_demo;CREATE TABLE device_rule_demo (    device_id INTEGER PRIMARY KEY,    site_id   INTEGER NOT NULL,    code      TEXT NOT NULL,    serial_no TEXT,    UNIQUE (site_id, code),    UNIQUE (serial_no));INSERT INTO device_rule_demo(site_id, code, serial_no)VALUES (1, 'PUMP-007', 'SN-A1');-- Same code is allowed at another site:INSERT INTO device_rule_demo(site_id, code, serial_no)VALUES (2, 'PUMP-007', 'SN-B1');-- Same site + code is not allowed:INSERT INTO device_rule_demo(site_id, code, serial_no)VALUES (1, 'PUMP-007', 'SN-C1');

The composite UNIQUE constraint models the actual business key: site_id plus code. This is stronger than making code globally unique when the requirements only promise uniqueness within a site.

SQLite PRIMARY KEY nuance: do not assume every form is identical

Chapter 3 showed that an exact INTEGER PRIMARY KEY in a normal rowid table aliases the rowid. There is another SQLite-specific historical behavior: in an ordinary non-STRICT rowid table, a PRIMARY KEY that is not an INTEGER PRIMARY KEY can accept NULL unless you also declare NOT NULL. WITHOUT ROWID and STRICT tables enforce PRIMARY KEY non-nullability, and an INTEGER PRIMARY KEY never stores NULL—supplying NULL asks SQLite to allocate a rowid value.

DROP TABLE IF EXISTS legacy_pk;CREATE TABLE legacy_pk (    code TEXT PRIMARY KEY,    label TEXT);INSERT INTO legacy_pk(code, label) VALUES (NULL, 'first null key');INSERT INTO legacy_pk(code, label) VALUES (NULL, 'second null key');SELECT rowid, quote(code), label FROM legacy_pk;DROP TABLE IF EXISTS explicit_pk;CREATE TABLE explicit_pk (    code TEXT PRIMARY KEY NOT NULL,    label TEXT);-- This now fails:INSERT INTO explicit_pk(code, label) VALUES (NULL, 'rejected');

For new schemas, do not build a design around the historical NULL allowance. If a non-integer primary key is logically mandatory, say so explicitly with NOT NULL, or use STRICT/WITHOUT ROWID where appropriate.

CHECK: row-local predicates, with SQL NULL semantics

A CHECK expression is evaluated when a row is inserted or updated. SQLite treats numeric zero as a violation. A non-zero result passes. Importantly, a NULL result also passes. Therefore a range check alone does not imply NOT NULL.

DROP TABLE IF EXISTS reading_rule;CREATE TABLE reading_rule (    reading_id INTEGER PRIMARY KEY,    temperature_c REAL,    severity INTEGER,    CHECK (severity BETWEEN 1 AND 5),    CHECK (temperature_c IS NULL OR temperature_c > -273.15));INSERT INTO reading_rule(temperature_c, severity) VALUES (20.5, 3);  -- passINSERT INTO reading_rule(temperature_c, severity) VALUES (NULL, NULL); -- both CHECKs passINSERT INTO reading_rule(temperature_c, severity) VALUES (-300, 2); -- failINSERT INTO reading_rule(temperature_c, severity) VALUES (10, 9);   -- fail

The second row is valid because the first CHECK sees NULL and the second explicitly allows NULL. If severity is required, add severity INTEGER NOT NULL CHECK(...). CHECK expressions may refer to columns in the same row, but SQLite does not allow subqueries inside a CHECK constraint. Rules such as “no more than three open notes per device” therefore need a different mechanism.

DEFAULT fills omitted values; it does not override explicit values

A default is used when an INSERT omits a column (or explicitly requests DEFAULT where the syntax permits). It is not a repair rule for an invalid explicit value. If a column is NOT NULL DEFAULT 1, explicitly inserting NULL still violates NOT NULL under the normal ABORT behavior.

DROP TABLE IF EXISTS default_demo;CREATE TABLE default_demo (    event_id    INTEGER PRIMARY KEY,    status      TEXT NOT NULL DEFAULT 'open',    created_at  TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,    token       INTEGER NOT NULL DEFAULT (40 + 2));INSERT INTO default_demo(event_id) VALUES (1);INSERT INTO default_demo(event_id, status) VALUES (2, 'closed');SELECT event_id, status, created_at, typeof(token) AS token_typeFROM default_demoORDER BY event_id;

Literal defaults are reused as values. Parenthesized constant expressions are evaluated for each inserted row, and CURRENT_TIME, CURRENT_DATE, and CURRENT_TIMESTAMP are special supported defaults. Default expressions cannot depend on another column, a table lookup, a bound parameter, or a subquery.

Column-level versus table-level syntax

Choose the form that communicates the rule. NOT NULL belongs to a column. A one-column UNIQUE or CHECK may be easiest to read beside the column. Composite keys and cross-column checks naturally belong at table level.

CREATE TABLE maintenance_window (    window_id   INTEGER PRIMARY KEY,    device_id   INTEGER NOT NULL,    starts_at   TEXT NOT NULL,    ends_at     TEXT,    status      TEXT NOT NULL DEFAULT 'planned'                CHECK (status IN ('planned','active','done','cancelled')),    UNIQUE (device_id, starts_at),    CHECK (ends_at IS NULL OR ends_at >= starts_at));

The final CHECK is a cross-column rule but still row-local: one row supplies both timestamps. It does not need to inspect any other row or table.

Lab: prove the FieldNotes constraint contract

Create a disposable table and run each numbered write separately. Predict the result before execution. When a statement fails, verify that the invalid row did not appear.

DROP TABLE IF EXISTS note_guard;CREATE TABLE note_guard (    note_id      INTEGER PRIMARY KEY,    device_code  TEXT NOT NULL,    external_ref TEXT,    severity     INTEGER NOT NULL DEFAULT 1 CHECK (severity BETWEEN 1 AND 5),    opened_at    TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,    closed_at    TEXT,    UNIQUE (device_code, external_ref),    CHECK (closed_at IS NULL OR closed_at >= opened_at));-- 1 validINSERT INTO note_guard(device_code, external_ref, severity, opened_at)VALUES ('PUMP-007','WO-100',4,'2026-08-12 07:00:00');-- 2 duplicate composite key -> UNIQUE failureINSERT INTO note_guard(device_code, external_ref, severity, opened_at)VALUES ('PUMP-007','WO-100',2,'2026-08-12 08:00:00');-- 3 missing required device -> NOT NULL failureINSERT INTO note_guard(device_code) VALUES (NULL);-- 4 out-of-range severity -> CHECK failureINSERT INTO note_guard(device_code, severity) VALUES ('FAN-014', 9);-- 5 closed before opened -> CHECK failureINSERT INTO note_guard(device_code, opened_at, closed_at)VALUES ('SENS-003','2026-08-12 10:00:00','2026-08-12 09:00:00');SELECT note_id, device_code, external_ref, severity, opened_at, closed_atFROM note_guard;

After running the statements one at a time, the final SELECT should show only the first row. The database is demonstrating the difference between “our application intends this rule” and “SQLite refuses to persist violations of this rule.”

Production judgment: make constraints intentional and diagnosable

Do not disable constraints to make imports or tests “work.” Stage questionable data separately, diagnose it, then move only valid rows into constrained final tables.

Summary and bridge

NOT NULL, UNIQUE, PRIMARY KEY, CHECK, and DEFAULT turn logical requirements into SQLite-enforced state rules. You also saw why SQLite-specific details matter: primary-key NULL behavior depends on table form, CHECK NULL results pass, and defaults are insertion-time values rather than universal repair logic. The next lesson adds relationships between tables—and an important SQLite requirement: foreign-key declarations and foreign-key enforcement are separate concerns.

Authoritative references

• SQLite — CREATE TABLE.
• SQLite — Datatypes and PRIMARY KEY quirks.
• SQLite — Core SQL syntax.
• SQLite — Release History.