Huny's Dev Blog
huny.dev

Fix SQLite Foreign Keys That Appear Not to Work

Enable SQLite foreign keys per connection outside a transaction, reproduce a no-op PRAGMA, and find existing orphan rows with foreign_key_check.

Hun Jang
Hun Jang Nov 19, 2026

If SQLite accepts a row without its referenced parent despite a REFERENCES clause, check PRAGMA foreign_keys on the current connection. During connection initialization, execute PRAGMA foreign_keys = ON outside a transaction and verify that the value is 1. Use PRAGMA foreign_key_check to inspect violations already stored. The Python example reproduces how the timing of the setting changes the result.

Two cyan stacks of data cards joined by an amber chain and connection latch

Check both the table definition and connection setting

Foreign key enforcement must be enabled per connection. SQLite’s default can depend on compile-time configuration, so set and read it explicitly. Changing foreign_keys while a transaction or SAVEPOINT is active has no effect. A statement completing without an error does not establish that enforcement is enabled. SQLite foreign keys · PRAGMA foreign_keys

With a connection pool, apply the initialization whenever the pool creates a new physical connection. A setting on one connection does not change another. Also check whether the ORM or driver opens a transaction before the query. SQLite foreign keys

Reproduce enabling enforcement inside a transaction

Save the code as sqlite_foreign_keys.py and run python3 sqlite_foreign_keys.py. It uses Python 3.12’s autocommit=True and explicit SQL transaction statements. The example first disables enforcement to make the reproduction deterministic. In an application, enable it during connection initialization. Python sqlite3 transaction control

Reject new violations and check existing data

The example ran on November 19, 2026 with Python 3.12.14 and an in-memory SQLite 3.53.1 database. It verified that the setting stayed at 0 after the enable request inside a transaction and that an orphan row was inserted. Enabling enforcement after the commit caused a new invalid insert to fail with SQLITE_CONSTRAINT_FOREIGNKEY.

Enabling enforcement alone does not repair existing violations. foreign_key_check reports the violating table, row identification, referenced table, and constraint identifier. The example inserts the missing parent and verifies that the violation disappears. For production data, decide whether restoring a parent, updating a child, or deleting a row matches the application’s meaning. PRAGMA foreign_key_check

Define the initialization order

Open the connection, enable foreign key enforcement before starting a transaction, read back the setting, then start application transactions. With Python’s autocommit=False, the driver keeps a transaction open, so design the initialization timing separately. The example verifies explicit SQL BEGIN and COMMIT while using autocommit=True. Python sqlite3 transaction control · PRAGMA foreign_keys

This article covers ordinary immediate foreign key checks. Deferred constraints can postpone reporting a violation until commit. For schemas with composite keys or incorrect referenced indexes, inspect those definitions as well. The experiment uses one connection to a local in-memory database and does not test connection pool integration. SQLite foreign keys

한국어 버전 · SQLite 외래 키가 작동하지 않을 때 PRAGMA와 트랜잭션 확인하기SQLite 외래 키가 작동하지 않을 때 PRAGMA와 트랜잭션 확인하기

추천 글