Generated Columns, Integrity Checks, and Enforcing Cross-Column Rules

Learning outcomes

The chapter has built integrity from simple column rules through relationship and failure semantics. The final step is operational: derive row-local values safely, inspect hidden generated-column metadata, run the correct integrity audits, and recognize the boundary where ordinary declarative constraints are no longer expressive enough.

Generated columns derive data from the same row

A generated column is computed from other columns rather than supplied directly by INSERT/UPDATE. SQLite added generated-column support in version 3.31.0. The expression is part of the schema contract, so every writer gets the same derived result.

DROP TABLE IF EXISTS work_measure;CREATE TABLE work_measure (    work_id          INTEGER PRIMARY KEY,    duration_minutes INTEGER NOT NULL CHECK (duration_minutes >= 0),    duration_hours   REAL GENERATED ALWAYS AS                     (duration_minutes / 60.0) VIRTUAL);INSERT INTO work_measure(work_id, duration_minutes) VALUES (1, 150);SELECT work_id, duration_minutes, duration_hoursFROM work_measure;

The expected derived value is 2.5 hours. Application code does not write duration_hours; changing duration_minutes changes what the generated column returns.

VIRTUAL versus STORED

A VIRTUAL generated column is computed when read. A STORED generated column is computed when the row is written and occupies database space. Both look like columns to SQL queries, and both can often participate in constraints/indexes. The performance tradeoff is workload-specific, so measure rather than assuming STORED is always faster.

DROP TABLE IF EXISTS note_metric;CREATE TABLE note_metric (    note_id          INTEGER PRIMARY KEY,    duration_minutes INTEGER NOT NULL CHECK (duration_minutes >= 0),    duration_hours   REAL AS (duration_minutes / 60.0) VIRTUAL,    is_long          INTEGER AS (duration_minutes >= 120) STORED                     CHECK (is_long IN (0,1)));CREATE INDEX idx_note_metric_is_long ON note_metric(is_long);INSERT INTO note_metric(note_id, duration_minutes) VALUES (1, 90), (2, 180);SELECT note_id, duration_hours, is_long FROM note_metric ORDER BY note_id;

SQLite does not permit adding a STORED generated column later with ordinary ALTER TABLE ADD COLUMN; VIRTUAL can be added. Treat STORED generated columns as a schema-compatibility decision, not a cosmetic optimization.

Generated expressions are intentionally limited

SQLite requires generated expressions to be deterministic and row-local. They may reference other columns in the row (including earlier generated dependencies that do not form a cycle), constants, and deterministic scalar functions. They may not use subqueries, aggregate functions, window functions, or table-valued functions. They cannot directly reference rowid, though an INTEGER PRIMARY KEY alias may be referenced by its declared name.

Constraints and indexes can make derived meaning searchable

Generated columns may carry datatype/affinity and several normal constraints, and they may be indexed. This is useful when a repeated row-local expression has stable business meaning.

DROP TABLE IF EXISTS device_code_rule;CREATE TABLE device_code_rule (    device_id INTEGER PRIMARY KEY,    site_code TEXT NOT NULL,    local_code TEXT NOT NULL,    canonical_code TEXT AS (upper(site_code || ':' || local_code)) VIRTUAL                   NOT NULL UNIQUE);INSERT INTO device_code_rule(site_code, local_code)VALUES ('north','pump-007');SELECT device_id, canonical_code FROM device_code_rule;PRAGMA table_info('device_code_rule');PRAGMA table_xinfo('device_code_rule');

table_info omits generated columns, while table_xinfo includes them with a hidden/generated indicator. That is another reason Chapter 3 taught table_xinfo as the more complete inspection tool.

Cross-column CHECK rules are powerful but still row-local

Generated columns are optional; sometimes a direct CHECK is clearer. For FieldNotes, a lifecycle row can require a closing time only when status is closed and can enforce ordering between timestamps.

DROP TABLE IF EXISTS lifecycle_rule;CREATE TABLE lifecycle_rule (    note_id   INTEGER PRIMARY KEY,    status    TEXT NOT NULL CHECK (status IN ('open','closed')),    opened_at TEXT NOT NULL,    closed_at TEXT,    CHECK (      (status = 'open'   AND closed_at IS NULL)      OR      (status = 'closed' AND closed_at IS NOT NULL AND closed_at >= opened_at)    ));INSERT INTO lifecycle_rule VALUES(1,'open','2026-08-12 06:00:00',NULL),(2,'closed','2026-08-12 06:00:00','2026-08-12 07:00:00');-- Invalid: closed but no closed_atINSERT INTO lifecycle_rule VALUES(3,'closed','2026-08-12 06:00:00',NULL);

This rule is an excellent database constraint because every necessary fact is in one row. A rule such as “a device may have at most three open notes” requires inspecting other rows, so CHECK/generated-column expressions cannot enforce it.

Three integrity PRAGMAs answer different questions

Do not treat the word “integrity” as one test. SQLite separates low-level/database-structure checks from foreign-key relationship checks.

PRAGMA quick_check;PRAGMA integrity_check;PRAGMA foreign_key_check;

A healthy database typically returns one row ok from quick/integrity checks and zero rows from foreign_key_check. If foreign keys were disabled while bad data was written, integrity_check can still report ok while foreign_key_check reports orphans.

FieldNotes integrity audit script

Save repeatable operational checks in a version-controlled SQL script. The CLI directives below make failures visible and keep the audit easy to rerun. Chapter 16 will cover backup/recovery before any repair operation.

-- sql/audit_integrity.sql.bail on.headers on.mode boxSELECT sqlite_version() AS sqlite_version;PRAGMA foreign_keys;SELECT 'quick_check' AS audit, * FROM pragma_quick_check;SELECT 'integrity_check' AS audit, * FROM pragma_integrity_check;-- Zero rows is the desired result:PRAGMA foreign_key_check;-- Schema evidence for generated/hidden columns:PRAGMA table_xinfo('maintenance_note');-- Domain-level row-local checks that are useful even if constraints exist:SELECT note_id, device_id, occurred_at, severity, note_textFROM maintenance_noteWHERE severity NOT IN ('info','warning','critical')   OR trim(note_text) = '';

The last query is not a substitute for a CHECK constraint; it is operational evidence and can help diagnose a legacy or migrated database. For a new database, prefer preventing invalid rows first and auditing second.

What ordinary constraints cannot express

SQLite constraints cover much more than many applications use, but they are not a general rule engine. UNIQUE can compare rows for uniqueness and foreign keys can enforce parent existence; CHECK/generated expressions cannot run arbitrary queries across tables or across time.

Triggers arrive in Chapter 12. Transaction/concurrency chapters will also matter because a cross-row invariant is only reliable if concurrent writers cannot both validate stale state and then commit conflicting changes.

Chapter lab: integrity audit with an intentionally created orphan

Use a disposable database. First create valid rows with enforcement on. Then deliberately turn enforcement off outside a transaction to simulate a legacy/import mistake, insert one orphan, and compare the audits.

PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS audit_note;DROP TABLE IF EXISTS audit_device;CREATE TABLE audit_device (    device_id INTEGER PRIMARY KEY,    code TEXT NOT NULL UNIQUE);CREATE TABLE audit_note (    note_id INTEGER PRIMARY KEY,    device_id INTEGER NOT NULL REFERENCES audit_device(device_id),    severity INTEGER NOT NULL CHECK (severity BETWEEN 1 AND 5));INSERT INTO audit_device VALUES (1,'PUMP-007');INSERT INTO audit_note VALUES (10,1,3);PRAGMA foreign_keys = OFF;INSERT INTO audit_note VALUES (11,999,4);PRAGMA foreign_keys = ON;PRAGMA integrity_check;PRAGMA foreign_key_check;-- Cleanup the intentional orphan:DELETE FROM audit_note WHERE note_id = 11;PRAGMA foreign_key_check;

The critical observation is that integrity_check can say ok while foreign_key_check reports the orphan. After cleanup, foreign_key_check should return zero rows.

Production review checklist

Summary and bridge to Chapter 6

Chapter 5 turned integrity into observable engine behavior: constraints reject invalid row states, foreign keys protect relationships only when enforcement is active, lifecycle actions encode ownership, deferred constraints move validation to COMMIT, conflict algorithms determine failure scope, and generated columns derive row-local meaning. You also learned that no single audit proves everything. Chapter 6 now uses this protected schema as the target for deliberate INSERT, UPDATE, DELETE, UPSERT, and RETURNING operations.

Authoritative references

• SQLite — Generated Columns.
• SQLite — PRAGMA integrity_check / quick_check / foreign_key_check.
• SQLite — CREATE TABLE.
• SQLite — STRICT Tables.
• SQLite — Release History.