integrity_check, quick_check, foreign_key_check, and Corruption Diagnosis

Learning outcomes

Corruption is a structural problem in the database image, but many serious data problems are not corruption at all. A database can be structurally perfect and still contain an orphaned foreign key, an impossible business state, stale external data, or the wrong application schema version. Operational checks therefore need layers.

Four layers of “healthy”

quick_check versus integrity_check

quick_check performs most of the checks of integrity_check but skips UNIQUE verification and table/index content-consistency verification. Current SQLite documentation describes quick_check as O(N) while a full integrity_check can require O(N log N) work.

PRAGMA quick_check;-- Healthy expected output:-- okPRAGMA integrity_check;-- Healthy expected output:-- ok

Both can accept limits/partial-table forms in current SQLite, but a partial check is intentionally narrower than a whole-database check. Do not report “database fully healthy” after checking one table.

Foreign keys are checked separately

SQLite explicitly documents that integrity_check does not detect foreign-key violations. This surprises people who read “integrity” as “all application integrity.” Use foreign_key_check separately.

PRAGMA foreign_keys = OFF;CREATE TABLE parent(id INTEGER PRIMARY KEY);CREATE TABLE child(    id INTEGER PRIMARY KEY,    parent_id INTEGER REFERENCES parent(id));INSERT INTO parent VALUES(1);INSERT INTO child VALUES(1,1);INSERT INTO child VALUES(2,999);   -- deliberate orphan while FK enforcement is offPRAGMA foreign_keys = ON;PRAGMA integrity_check;            -- can still report: okPRAGMA foreign_key_check;          -- reports the orphan

The local acceptance run returned ok from both structural checks and one row from foreign_key_check: child table child, rowid 2, parent table parent, FK index 0.

Business-rule checks belong beside SQLite checks

Even perfect SQL constraints cannot encode every rule that depends on external systems, time, policy, or aggregate state. A health report should therefore include explicit queries for critical assumptions.

-- Empty text may be syntactically legal but operationally useless.SELECT note_idFROM maintenance_noteWHERE trim(note_text) = '';-- Verify that every active device is attached to an active/known site-- according to the actual schema policy used by your application.SELECT d.device_id, d.device_codeFROM device AS dLEFT JOIN site AS s ON s.site_id = d.site_idWHERE s.site_id IS NULL;-- File identity and migration level.PRAGMA application_id;PRAGMA user_version;

Do not blindly copy these as universal rules. The point is to encode your invariants in a repeatable report with an owner and expected result.

A health-check script should fail clearly

from pathlib import Pathimport sqlite3, syspath = Path(sys.argv[1])con = sqlite3.connect(f"file:{path.as_posix()}?mode=ro", uri=True)try:    quick = con.execute("PRAGMA quick_check").fetchall()    integrity = con.execute("PRAGMA integrity_check").fetchall()    fk = con.execute("PRAGMA foreign_key_check").fetchall()    app_id = con.execute("PRAGMA application_id").fetchone()[0]    user_version = con.execute("PRAGMA user_version").fetchone()[0]    empty_notes = con.execute(        "SELECT count(*) FROM maintenance_note WHERE trim(note_text)=''"    ).fetchone()[0]    report = {        "quick": quick,        "integrity": integrity,        "foreign_key_violations": len(fk),        "application_id": app_id,        "user_version": user_version,        "empty_notes": empty_notes,    }    print(report)    healthy = (        quick == [("ok",)] and        integrity == [("ok",)] and        not fk and        empty_notes == 0    )    raise SystemExit(0 if healthy else 2)finally:    con.close()

Opening read-only is useful for an observation job, but remember Chapter 9/12 caveats for read-only WAL databases and immutable mode. Your operational tool must use a connection mode compatible with how the deployment stores live state.

Representative failures: preserve evidence first

What not to do during corruption diagnosis

Do not edit database pages with a hex editor, delete WAL/journal files by hand, run random PRAGMAs from forum snippets, or repeatedly write to the only damaged copy. Those actions can destroy recoverable evidence. Make a protected copy first, identify whether a good backup exists, and perform recovery experiments on disposable duplicates.

Routine cadence versus incident checks

Checkpoint

Bridge to salvage and restore drills

When a known-good backup exists, restore is usually safer than salvage. When it does not, SQLite’s .recover can attempt to extract surviving content from damaged pages—but recovery output must be treated as suspect data until rebuilt and validated.

Authoritative references

• SQLite PRAGMA documentation
• SQLite Database Corruption guidance
• SQLite Recovery documentation