Comparing SQLite's PRAGMA quick_check and integrity_check: Detecting database corruption via read operations

About this article
This article was generated using an automated workflow powered by generative AI. Referencing the official SQLite PRAGMA specification, it explains the differences between quick_check, integrity_check, and foreign_key_check using safe in-memory database samples. Execution on physical hardware has not been verified.

Verification status: 📘 Official SQLite documentation verified / Hardware execution unverified

To verify whether an SQLite database is healthy, you sometimes usePRAGMA integrity_check.okIs the output"good enough for structural checks, but it does not guarantee the validity of business data, including foreign keys."The answer is

SQLite providesquick_checkandintegrity_check, as well as a separateforeign_key_check. By deliberately creating data with foreign key violations, we compare the differences among these three.

Three types of inspections

SQLite'sofficial PRAGMA documentationexplains that integrity_check does not detect foreign key violations and that foreign_key_check must be used.

Try it without breaking anything

The following code prepares an in-memory DB without using a file. No external libraries beyond Python's standard library sqlite3 are required.:memory:volatile DB. No external libraries beyond Python's standard library sqlite3 are required.

import sqlite3

with sqlite3.connect(":memory:") as db:

    # 教材用の接続内だけFK強制をOFFにする

    db.execute("PRAGMA foreign_keys=OFF")
    db.executescript("""
        CREATE TABLE parent (id INTEGER PRIMARY KEY);
        CREATE TABLE child (
            id INTEGER PRIMARY KEY,
            parent_id INTEGER REFERENCES parent(id)
        );
        INSERT INTO parent(id) VALUES (1);
        INSERT INTO child(id, parent_id) VALUES (10, 99);
    """)
    db.commit()
    db.execute("PRAGMA foreign_keys=ON")

    for label, statement in [
        ("quick_check", "PRAGMA quick_check"),
        ("integrity_check", "PRAGMA integrity_check"),
        ("foreign_key_check", "PRAGMA foreign_key_check")
    ]:
        print(label, db.execute(statement).fetchall())

You can test without touching existing DBs. Run with the following command.

python3 check-db.py

The full file isDaily-Code-Samples: Comparing SQLite Checkssaved in.

Expected output

quick_check [('ok',)]
integrity_check [('ok',)]
foreign_key_check [('child', 10, 'parent', 0)]

This is an expected execution result example, not a transcription from a real machine. Since the parent table has only ID=1 and the child table has a record referencing a non-existent parent ID=99, the violation is expected to be found only in the final check.

The DB is readable, the structure is sound, and the data semantics are correctare separate conditions.

Change one place to see the difference

Change this line in the sample.

-- 変更前
INSERT INTO child(id, parent_id) VALUES (10, 99);

-- 変更後
INSERT INTO child(id, parent_id) VALUES (10, 1);

To reference an existing parent, after the modification,foreign_key_checkshould no longer return violations. We can expect both structural checks tookremain unchanged.

Another observation point isPRAGMA foreign_keys=ONthe position of. Switching while a transaction has already started may sometimes fail to take effect as expected. That is whycommit()is specified explicitly this time.

Why Separate the Checks?

Verifying the consistency between SQLite indexes and row data requires examining the file structure. On the other hand, foreign key reference relationships pertain to the relations between values within tables.

For example, even if an order detail references a customer ID that does not exist in the customer master, it does not necessarily mean the SQLite file is corrupted. Just because the internal structure check succeeds does not guarantee the consistency of sales aggregation or billing.

Conversely, even if business data reference relationships appear correct, it does not guarantee the absence of disk failures. It is important not to confuse checks with different roles.

Precautions for Production Databases

This demo turns off foreign key constraint enforcement to create violating rows, butdo not replicate this in production databases. There is no need to disable constraints in order to diagnose an existing database.

When targeting an actual database, first consider the inspection time relative to data volume, backups, WAL mode, concurrent updates by other processes, and permissions. Even read operations can generate disk I/O and locking loads.

Even if an error occurs during inspection, do not delete files or execute REINDEX without understanding the cause; preserve backups and investigate the root cause.

Operational Usage Guidelines

• Routine lightweight monitoring: quick_check is a candidate. However, understand the inspection items that are omitted.

• When inconsistency is suspected: check the detailed results of integrity_check.

• Foreign key consistency: perform foreign_key_check separately.

• Even so, it does not necessarily mean the business values are correct, so cross-reference them with the application checks.

Official Information

• SQLite — PRAGMA quick_check

• SQLite — PRAGMA integrity_check

• SQLite — PRAGMA foreign_key_check