Enable and prove a foreign-key constraint in memory

By Batu · English technical notes

Also published in our primary archive.

An SQLite foreign-key declaration is only useful when enforcement is enabled for the connection. This example opens the standard-library sqlite3 module’s special ":memory:" database, executes PRAGMA foreign_keys = ON, and reads the pragma back before creating tables. That read-back is important: it confirms the connection setting used by this program, rather than merely assuming the command succeeded.

parent_id in child references parent(id). After inserting parent 1 and its valid child, the program deliberately tries to insert a child with parent 99. SQLite raises sqlite3.IntegrityError; the handler records the expected rejection. The final query and assertion show that the failed statement did not add a second child row. Exact output reports the enabled setting, the rejected input, and the remaining row count.

Foreign-key enforcement is connection-specific in SQLite, so another connection must enable it too. Also, a nullable child key has special semantics: NULL does not require a matching parent. Add NOT NULL when the relationship itself must be required. This is an immediate constraint demonstration, not a proof of behavior under concurrent connections, deferred constraints, or every future schema migration. It uses no newer sqlite3 API and runs on Python 3.6+.

For the connection and parameter-binding APIs, see the Python sqlite3 documentation. SQLite documents that foreign keys are disabled by default and describes their enforcement in Foreign Key Support.

AI assistance disclosure: This article was drafted with AI assistance and should be adapted to the application’s schema rules.

import sqlite3

con = sqlite3.connect(":memory:")
try:
    con.execute("PRAGMA foreign_keys = ON")
    enabled = con.execute("PRAGMA foreign_keys").fetchone()[0]
    assert enabled == 1

    con.executescript("""
        CREATE TABLE parent (id INTEGER PRIMARY KEY);
        CREATE TABLE child (
            id INTEGER PRIMARY KEY,
            parent_id INTEGER NOT NULL REFERENCES parent(id)
        );
    """)
    con.execute("INSERT INTO parent(id) VALUES (?)", (1,))
    con.execute("INSERT INTO child(id, parent_id) VALUES (?, ?)", (10, 1))

    try:
        con.execute("INSERT INTO child(id, parent_id) VALUES (?, ?)", (11, 99))
    except sqlite3.IntegrityError:
        missing_parent_rejected = True
    else:
        missing_parent_rejected = False

    child_count = con.execute("SELECT COUNT(*) FROM child").fetchone()[0]
    assert missing_parent_rejected is True
    assert child_count == 1

    print("foreign keys: on")
    print("missing parent: rejected")
    print("child rows: 1")
finally:
    con.close()

foreign keys: on
missing parent: rejected
child rows: 1