Python SQLite foreign keys: enable enforcement on each connection

Operation contract

The owned in-memory connection enables the setting before a transaction, creates a customer and a receipt that references it, then attempts an orphan receipt. The orphan raises IntegrityError and does not appear in the final count. The fixture checks the setting so a default configuration cannot silently change the lesson result.

Failure and ownership boundary

Enable the PRAGMA separately on every new connection before writes; declaring REFERENCES alone is insufficient on configurations where foreign keys are off. This does not replace authorization or a production migration. Python sqlite3 transactions: bind values and roll back failed batches, Python SQLite writer contention: reject a busy transaction before changing state and Python Django database constraints: enforce receipt identity per owner cover related state rules.

Working program

import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("PRAGMA foreign_keys=ON")
print("enabled:", connection.execute("PRAGMA foreign_keys").fetchone()[0])
connection.execute("CREATE TABLE customer(id INTEGER PRIMARY KEY)")
connection.execute("CREATE TABLE receipt(id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customer(id))")
with connection:
    connection.execute("INSERT INTO customer VALUES (41)")
    connection.execute("INSERT INTO receipt VALUES (1, 41)")
try:
    with connection:
        connection.execute("INSERT INTO receipt VALUES (2, 99)")
except sqlite3.IntegrityError:
    print("orphan rejected")
print("receipts:", connection.execute("SELECT COUNT(*) FROM receipt").fetchone()[0])
connection.close()

Output

enabled: 1
orphan rejected
receipts: 1

Costs and limits

The indexed parent-key lookup adds database work to each child write; actual cost depends on schema and workload. This small in-memory fixture says nothing about disk durability or multi-connection deployment behavior.

Common Mistakes

• A REFERENCES clause alone does not prove this connection enforces it.
• A foreign key enforces existence, not tenant permission.

Connected lessons

Python sqlite3 transactions: bind values and roll back failed batches, Python SQLite writer contention: reject a busy transaction before changing state, Python Django database constraints: enforce receipt identity per owner.