How to check database integrity

Run PRAGMA integrity_check and read the answer, which is the word ok or a list of faults. Run PRAGMA quick_check next and compare. On a file with one damaged table page, quick_check answered ok while integrity_check answered row 12345 missing from index product_sku. The two checks look at different things.

Why check this

Run this after restoring a backup, after a crash, and before a migration, because rebuilding a table on a damaged file spreads the damage into the new one.

The failure it catches is a query that returns the wrong answer without failing. A table row and its index entry disagree, so the same WHERE sku = ... returns one row through a scan and no rows through an index seek, depending on which plan the optimiser picks. Nothing in the application log marks this as an error.

What the check cannot do is tell you the data is right. A file can hold orphan rows, duplicated customers and negative prices and still answer ok. The question it answers is about file structure, not about meaning.

Prerequisites

• sqlite3 3.50.6, 32-bit, the Android SDK platform-tools build at /c/Android/sdk/platform-tools/sqlite3.exe. PostgreSQL and MySQL have no equivalent pragma; their checks are amcheck and CHECK TABLE.
• Node 22.23.2 with the bundled node:sqlite, linking SQLite 3.51.3, to build a file and damage a copy of it.
• A copy. Never patch bytes in a file anything else has open.
• The SQLite pragma reference.
• build-app.mjs, a 20000-row table with one index.

import { DatabaseSync } from 'node:sqlite';
const db = new DatabaseSync('app.db');
db.exec('CREATE TABLE product (id INTEGER PRIMARY KEY, sku TEXT NOT NULL, price_cents INTEGER NOT NULL)');
db.exec('CREATE INDEX product_sku ON product(sku)');
const ins = db.prepare('INSERT INTO product (id, sku, price_cents) VALUES (?, ?, ?)');
db.exec('BEGIN');
for (let i = 1; i <= 20000; i++) ins.run(i, 'SKU-' + String(i).padStart(6, '0'), 100 + (i % 900));
db.exec('COMMIT');
console.log('rows', db.prepare('SELECT count(*) AS n FROM product').get().n);
db.close();

• bend.mjs, which copies that file and changes one character inside one table page, leaving the index entry untouched. That is how a bad sector presents itself.

import { readFileSync, writeFileSync, copyFileSync } from 'node:fs';
copyFileSync('app.db', 'app-bent.db');
const buf = readFileSync('app-bent.db');
const off = buf.indexOf(Buffer.from('SKU-012345'));
const pageStart = Math.floor(off / 4096) * 4096;
console.log('page type 0x' + buf[pageStart].toString(16) + ' (0x0d is a table leaf)');
buf[off + 4] = '9'.charCodeAt(0);
writeFileSync('app-bent.db', buf);
console.log('byte', off + 4, 'changed: SKU-012345 is now SKU-912345 in the table only');

• orphan.sql for step 7: a sound file holding one order that points at a customer who does not exist. Load it with sqlite3 orphan.db < orphan.sql.

CREATE TABLE customer (
  id      INTEGER PRIMARY KEY,
  country TEXT NOT NULL
);
CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customer(id),
  total_cents INTEGER NOT NULL
);
INSERT INTO customer (id, country) VALUES (1, 'UA');
INSERT INTO orders (id, customer_id, total_cents) VALUES (10, 1, 2500), (12, 999, 4200);

Steps

1. 1

   Step 1.

   Run the fast check on the sound file first, so you know what a pass looks like.

   sqlite3 app.db "PRAGMA quick_check;"
   

   ok

   ok on one line is the only passing answer. Anything else is a list of faults.

2. 2

   Step 2.

   Run the full check on the same file.

   sqlite3 app.db "PRAGMA integrity_check;"
   

   ok

   Both pass, so the two answers carry no information yet. Step 4 is where they part.

3. 3

   Step 3.

   Copy the file and change one character in one table page.

   node --no-warnings bend.mjs
   

   page type 0xd (0x0d is a table leaf)
   byte 522570 changed: SKU-012345 is now SKU-912345 in the table only

   The first byte of a page names its kind, and 0x0d is a table leaf. The index leaf holding the same text is on the next page and still reads SKU-012345.

4. 4

   Step 4.

   Run the fast check on the damaged copy.

   sqlite3 app-bent.db "PRAGMA quick_check;"
   

   ok

   A clean pass on a file that is now internally inconsistent. quick_check walks the page structure of every b-tree and checks that records are well formed. It never compares an index against the table it indexes.

5. 5

   Step 5.

   Run the full check on the same damaged copy.

   sqlite3 app-bent.db "PRAGMA integrity_check;"
   

   row 12345 missing from index product_sku

   integrity_check does the comparison quick_check skips: it reads every row, recomputes what each index entry should be, and matches them. Row 12345 holds a value the index has no entry for.

6. 6

   Step 6.

   Ask the damaged file one question two ways, and read the two answers.

   sqlite3 app-bent.db ".mode box" "SELECT 'index seek' AS route, count(*) AS rows_found FROM product WHERE sku = 'SKU-912345' UNION ALL SELECT 'table scan', count(*) FROM product NOT INDEXED WHERE sku = 'SKU-912345';"
   

   ┌────────────┬────────────┐
   │   route    │ rows_found │
   ├────────────┼────────────┤
   │ index seek │ 0          │
   │ table scan │ 1          │
   └────────────┴────────────┘

   The row is there and it is not there. EXPLAIN QUERY PLAN on the first branch reports SEARCH product USING COVERING INDEX product_sku: that plan reads the index and never opens the table. NOT INDEXED forces the scan, which finds the row.

7. 7

   Step 7.

   Run the full check on a file whose data is wrong in a way the engine does not police.

   sqlite3 orphan.db "PRAGMA integrity_check; PRAGMA foreign_key_check;"
   

   ok
   orders|12|customer|0

   ok from integrity_check and an orphan order from foreign_key_check, on the same file in the same second. Structural soundness and correct data are separate questions.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | ok from integrity_check | The pages, records and indexes agree with each other | Nothing here. The data may still be wrong. Check constraints separately. | | A list of faults | The file is damaged | Do not write to it. Dump what reads, rebuild, restore from backup if the dump is short. | | row N missing from index X | A table row and its index disagree | REINDEX repairs this one if the table side is the good copy. Confirm which side is right first. | | ok from quick_check alone | Page structure is sound | It proves less than the full check. Run integrity_check before you clear a restore. | | Two plans, two row counts | Index and table hold different values | Treat every query result from that file as unreliable until the check passes. | | ok plus rows from foreign_key_check | The file is sound and the data is not | Fix the orphans. No pragma is going to report them as corruption. |

Thresholds

Common mistakes

What to check next

• How to check foreign key: the orphan rows that integrity_check reports as ok.
• How to check duplicate rows in sql: the other kind of wrong data no structural check will mention.
• How to verify a database backup: where this check belongs in the restore procedure.
• How to check data integrity after migration: comparing content before and after, which is a different question again.

FAQ

What is the difference between integrity_check and quick_check?

quick_check validates page structure and record format. integrity_check does that and also compares every index entry against the row it points at. The file in step 4 passed quick_check and failed integrity_check on exactly that difference.

How do I check data integrity in SQL?

Structural checks do not answer this. Use PRAGMA foreign_key_check for orphans, grouped counts for duplicates, and CHECK constraints for business rules. Run them as a set.

Can integrity_check repair the database?

No. It reports. REINDEX rebuilds indexes from table data, which fixes a mismatch when the table side is the good copy. When the table side is damaged, the answer is a dump and rebuild, or the backup.

How long does the check take on a large database?

It reads every page, so it scales with file size. On the 20000-row, 828 KB file above, integrity_check took 54 ms and quick_check 36 ms under time in Git Bash. Measure your own file rather than scaling that.

Verified