(not sure what the right category is… maybe this is a documentation issue? )
For backwards compatibility, foreign keys are disabled in SQLite by default (SQLite Foreign Key Support), and you need to enable them explicitly:
Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection. (Note, however, that future releases of SQLite might change so that foreign key constraints enabled by default. Careful developers will not make any assumptions about whether or not foreign keys are enabled by default but will instead enable or disable them as necessary.)
You might expect that you enable foreign keys using Python’s sqlite3 module like this:
from contextlib import closing
import sqlite3
print(sqlite3.sqlite_version)
with closing(sqlite3.connect(":memory:", autocommit=False)) as conn:
conn.execute("pragma foreign_keys = on")
conn.execute("create table fruits(id primary key)")
conn.execute("create table fruit_names(fruit_id references fruits(id), name)")
# you would expect this line to fail, since there's no fruit with ID 1:
conn.execute("insert into fruit_names(fruit_id, name) values (1, 'banana')")
conn.commit()
print("all good!")
3.51.2
all good!
As you can see, foreign keys are surprisingly not enforced. That’s because autocommit=False (which is listed as the recommended way to use sqlite3) starts a transaction, and once a transaction it active, PRAGMA foreign_keys has no effect:
It is not possible to enable or disable foreign key constraints in the middle of a multi-statement transaction (when SQLite is not in autocommit mode). Attempting to do so does not return an error; it simply has no effect.
So what’s the recommended way to enable foreign keys? In my own program, I made a helper function for opening a connection, and it calls sqlite3.connect with isolation_mode=None, runs pragma foreign_keys=on, and then runs conn.execute("begin deferred") manually.