Diagnose foreign-key failures and missing enforcement

Understand the likely causes

• Enforcement may differ by connection, a required parent may not exist, a parent key may violate uniqueness/collation rules, or a deletion may conflict with retained child records.
• A setting changed inside an active transaction may not take effect. Deferred constraints can report failure at a later check boundary than an immediate constraint, so transaction ownership matters.

Inspect the actual connection policy

Check PRAGMA foreign_keys on the same connection that performs the operation, before opening the relevant transaction. Do not infer policy from another shell or framework process. Record engine version, schema version, exact relationship, and transaction state. A FOREIGN KEY declaration and an active enforcement policy are separate facts; turning enforcement off is not a repair for incorrect records.

Compare key shape and business existence

Verify parent key columns, required uniqueness, and collation compatibility. Check child values using bound parameters and the intended relationship grain. An optional NULL key differs from an unknown required identifier; NOT NULL is needed when absence is forbidden. For a delete, inspect the intended RESTRICT, SET NULL, or CASCADE policy and retention requirements rather than selecting the action that merely removes the error.

Test rejection and repair in memory

The program enables enforcement explicitly, rejects a reference to parent9, and only then creates the parent and valid child. It prints the extended rejection identity and zero final violations. All work uses one fresh :memory: database closed by its owner. This does not authorize inserting a fabricated parent into real business data; a real parent must be established from valid domain evidence.

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:", isolation_level=None)) as db:
    db.execute("PRAGMA foreign_keys=ON")
    db.executescript("CREATE TABLE parent(id INTEGER PRIMARY KEY); CREATE TABLE child(id INTEGER PRIMARY KEY,parent_id INTEGER NOT NULL REFERENCES parent(id));")
    print("enforced", db.execute("PRAGMA foreign_keys").fetchone()[0])
    try:
        db.execute("INSERT INTO child VALUES(1,9)")
    except sqlite3.IntegrityError as error:
        print("rejected", error.sqlite_errorname)
    else:
        raise AssertionError("expected relationship rejection")
    db.execute("INSERT INTO parent VALUES(9)")
    db.execute("INSERT INTO child VALUES(1,9)")
    print("violations", len(db.execute("PRAGMA foreign_key_check").fetchall()))

enforced 1
rejected SQLITE_CONSTRAINT_FOREIGNKEY
violations 0

Repair data or schema under its owning transaction

Choose the fix supported by evidence: correct the child key, create an actually valid missing parent, reject an invalid request, or perform a reviewed schema migration. Preserve all required business changes in one transaction where appropriate. Do not catch the failure and assume earlier transaction work rolled back automatically; inspect state and let the owner clean up. Deferred violations must be resolved before the required commit boundary.

Keep it from recurring

• Establish and verify connection policy at initialization, keep parent/child key contracts explicit, index child access where justified, and regression-test every writer and migration path. Retain source identity and rejection context without exposing personal rows.