Python sqlite3 foreign keys stay off with autocommit=False: initialize and verify the connection

By ON Lab · Sources checked November 8, 2026

With Python's modern sqlite3 transaction control, autocommit=False opens a transaction immediately. SQLite's PRAGMA foreign_keys=ON does not change enforcement inside a transaction. Initialize enforcement on a fresh connection before switching to that mode, or use a supported connection-configuration API, then read back the setting and test the actual constraint.

A June 2026 reader using Python 3.13.3 on Windows observed that a cascade deleted child rows with the older default connection behavior but left them behind after adding autocommit=False. This is evidence of a specific historical reader problem, not proof of current search volume. We reproduced the timing issue with original fixtures on CPython 3.13.18 and SQLite 3.53.1 on macOS arm64.

Read the setting instead of trusting a successful execute call

import sqlite3

con = sqlite3.connect(":memory:", autocommit=False)
print(con.in_transaction)
con.execute("PRAGMA foreign_keys=ON")
print(con.execute("PRAGMA foreign_keys").fetchone())
con.close()

Our result was an active transaction and (0,): the statement ran without enabling enforcement. A returned cursor or the absence of an exception does not prove that this PRAGMA changed the connection.

SQLite documents the transaction restriction. Python documents that its autocommit=False mode maintains an open transaction, including opening another after a commit or rollback. These two behaviors explain why an apparently reasonable initialization order can fail.

Compare the four setup paths

Each native test created an original projects-and-tasks schema with an ON DELETE CASCADE relationship, inserted one parent and two children, deleted the parent, and tried to insert an orphan child.

The existing reader answer already identifies setconfig and the commit limitation. Our additional checks compare both initialization routes, verify rejection as well as cascading, inspect a fresh second connection, and demonstrate why changing an active connection to autocommit mode can commit pending work.

Initialize a fresh connection before entering the transaction mode

def open_with_foreign_keys(path):
    con = sqlite3.connect(path, autocommit=True)
    try:
        con.execute("PRAGMA foreign_keys=ON")
        if con.execute("PRAGMA foreign_keys").fetchone() != (1,):
            raise RuntimeError("Foreign-key enforcement is unavailable")
        con.autocommit = False
        return con
    except Exception:
        con.close()
        raise

This helper targets Python versions supporting the autocommit argument, introduced in Python 3.12. Start with an isolated :memory: database to verify your runtime. The helper creates a new connection, applies and verifies the setting outside a transaction, then enters the requested transaction mode before returning it.

In our fixture, the setting stayed enabled after entering autocommit=False and after committing the inserted data. Deleting the parent removed both children, and an invalid parent reference raised sqlite3.IntegrityError. Close the returned connection when finished, and use explicit commit or rollback according to your application's transaction boundary.

The connection-configuration alternative

con = sqlite3.connect(":memory:", autocommit=False)
con.setconfig(sqlite3.SQLITE_DBCONFIG_ENABLE_FKEY, True)
assert con.execute("PRAGMA foreign_keys").fetchone() == (1,)

This route also passed our cascade and orphan-insert checks. The Python API and the required configuration constant must be available in the actual Python and SQLite build. Check that support before using the alternative; a version label alone is not a replacement for a readback and a constraint test.

This is a different interface from issuing the PRAGMA inside an active transaction. Do not infer that the failing PRAGMA was secretly effective because another API can enable the feature.

Why commit followed by PRAGMA was still ineffective

con = sqlite3.connect(":memory:", autocommit=False)
con.commit()
print(con.in_transaction)
con.execute("PRAGMA foreign_keys=ON")

The transaction-state readback remained true in our test. The new transaction was already active when the PRAGMA ran, so enforcement stayed at zero. Repeating empty commits is not a route out of the documented transaction behavior.

Changing an active connection can commit pending work

The fresh-connection helper is deliberately an initialization routine. Do not transplant its mode change blindly into a connection already carrying application work.

In a separate original scratch-file test, we inserted a row in an uncommitted transaction, then set con.autocommit=True. After closing and reopening the database, that row was present. Python documents that this transition commits a pending transaction. Decide what should happen to that work before changing the mode; do not treat the switch as a harmless setting toggle.

Verify each newly opened connection

We enabled enforcement on one connection to a scratch database, closed it, and opened a fresh connection to the same file. The first read back one and the second read back zero in our build. A file containing foreign-key declarations does not establish that every future connection enforces them.

Initialize and verify each connection through the application's actual connection-creation route. Pools and ORMs may own that lifecycle, so an initialization call on an unrelated connection is not evidence that the application's connection is configured. We did not test any pool or ORM here.

Enabling enforcement does not repair existing orphan rows

Our separate in-memory control started with two orphan children while enforcement was disabled. Enabling it afterwards left both rows in place. PRAGMA foreign_key_check reported the two violations; it did not delete or repair them.

Keep diagnosis separate from data repair. Inspect existing violations and define the intended treatment of the affected records before modifying a real database. A successful future cascade is not proof that older inconsistent data has been cleaned up.

Record the runtime and validate the actual behavior

import sys
print(sys.version)
print(sqlite3.sqlite_version)
print(con.in_transaction)
print(con.execute("PRAGMA foreign_keys").fetchone())

Then test the intended deletion and one deliberately invalid reference on disposable data. Our results cover modern CPython and the bundled SQLite build used above, with in-memory and original scratch-file databases. They do not establish a Windows reproduction, an application-wide migration, or safe production cleanup.

Sources

• stackoverflow.com: source guidance
• docs.python.org: source guidance
• www.sqlite.org: source guidance
• www.sqlite.org: source guidance