Find foreign-key violations

Before you begin

• Python 3.12 or later.
• An existing SQLite database file that you are authorized to read.

Set up your example

1. Save as sqlite-foreign-key-check.py.
2. Open a terminal in its folder.
3. Run python "sqlite-foreign-key-check.py" --help and prepare the listed input files.
4. Save the supplied sample.sql and create_sample_db.py together in the same folder.
5. For the included educational fixture, run python create_sample_db.py --output sample.db. It refuses to overwrite an existing file.
6. The fixture intentionally contains duplicate customer emails, one NULL email, and one orphan order so audit examples have visible findings.

Sample files in this download:

• sample.sql
• create_sample_db.py

Download the ZIP, extract it, and open a terminal inside the extracted folder. The source file is sqlite-foreign-key-check.py.

Run the script

python "sqlite-foreign-key-check.py"

If your system uses python3 instead of python, use that command for Python examples. Keep paths containing spaces in quotes. Required inputs and optional settings are described in the code and setup steps.

What to expect

The complete script

#!/usr/bin/env python3
"""Report child rows that reference missing parent records using SQLite foreign_key_check."""
import argparse
import sys
import json
from pathlib import Path
import sqlite3
import time
from contextlib import closing

def identifier(value):
    return '"'+value.replace('"','""')+'"'

def connect(path):
    path=path.resolve()
    if not path.is_file():raise ValueError('Database must already exist')
    connection=sqlite3.connect(path.as_uri()+'?mode=ro',uri=True,timeout=5)
    connection.row_factory=sqlite3.Row
    connection.execute('PRAGMA query_only=ON')
    deadline=time.monotonic()+20
    connection.set_progress_handler(lambda: int(time.monotonic()>deadline),10000)
    return connection

def table(connection,name):
    if not connection.execute("SELECT 1 FROM sqlite_schema WHERE type='table' AND name=?",(name,)).fetchone():raise ValueError('Unknown table: '+name)
    return identifier(name)

def column(connection,name,field):
    names=[row['name'] for row in connection.execute('PRAGMA table_info('+table(connection,name)+')')]
    if field not in names:raise ValueError('Unknown column: '+field)
    return identifier(field)

def json_default(value):
    if isinstance(value,bytes):return {'blob_hex':value.hex()}
    raise TypeError(type(value).__name__)

def emit(rows):
    print(json.dumps([dict(row) for row in rows],indent=2,default=json_default))

def main():
    parser = argparse.ArgumentParser(description='Report child rows that reference missing parent records using SQLite foreign_key_check.')
    parser.add_argument("--database",type=Path,default=Path("sample.db"))
    args = parser.parse_args()
    try:
        with closing(connect(args.database)) as connection:
            emit(connection.execute('PRAGMA foreign_key_check'))
    except sqlite3.Error as exc:
        raise RuntimeError(str(exc))

if __name__ == '__main__':
    try:
        main()
    except (OSError, ValueError, RuntimeError) as exc:
        print(f'Error: {exc}', file=sys.stderr)
        sys.exit(1)

Open source as plain text · Download source and sample files

How it works

1. Report child rows that reference missing parent records using SQLite foreign_key_check.
2. Arguments are explicit and expected input errors produce a message with a nonzero exit code.
3. Results reflect your selected local data or the specific endpoint you choose; the example illustrates the result shape.

Make it your own

Adapt the task

Use PRAGMA foreign_key_check("orders") in source for one child table. The check reads violations even if enforcement was previously disabled.

Adjust presentation

Edit final print statements or json.dumps indentation to adjust the report layout. Keep input validation and exclusive-create file modes intact.

Change one setting at a time, run it again on sample data, and compare the output. The customization guide walks through this process.

If something goes wrong

Reference & next steps

• Python sqlite3 documentation ↗
• SQLite documentation ↗

These official references explain the underlying tools. The Scriptage example and guide are original instructional content. See verification notes for what has been checked and the limits of those checks.

Copies a template for your own notes. It does not send anything.