SQLite Ignored My ON DELETE CASCADE: Foreign Keys Are Off Until Each Connection Turns Them On

A small SQLite file indexes a folder of camera clips: one table of cameras, one table of clips, and each clip row points at the camera that recorded it. The schema says REFERENCES cameras(id) ON DELETE CASCADE. Someone removes the porch camera from the index. The porch clips are supposed to go with it. They stay, pointing at a camera that no longer exists, and a script that later joins clips to cameras quietly drops them from every report.

The schema was fine. The rule was written down; SQLite just was not applying it.

Disclosure: I make an Android camera app. Nothing below uses its code or describes how it stores anything. Everything here ran on one Linux machine with Python 3.10.12 and the SQLite library it links (3.37.2), against databases the scripts create themselves.

What the tests showed:

• A declared foreign key is not enforced by default. The delete left orphans and an insert pointing at a camera that was absent went through without an error.
• Enforcement belongs to the connection, not the file. Turning it on in one connection did nothing for a second connection opened on the same database.
• Turning it on inside a transaction is silently ignored, and Python's sqlite3 module had opened a transaction behind the script's back.
• Turning it on late does not clean anything up. Rows that were already orphaned stayed orphaned until a query removed them.
• PRAGMA foreign_key_check finds the damage, and a small connect() wrapper that refuses to return a connection without enforcement prevents it.

The setup

setup.py builds the index, deletes camera 1, and inserts a clip for camera 9, which does not exist.

import sqlite3

con = sqlite3.connect("clips.db")
con.executescript("""
CREATE TABLE cameras(
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE clips(
  id        INTEGER PRIMARY KEY,
  camera_id INTEGER NOT NULL REFERENCES cameras(id) ON DELETE CASCADE,
  path      TEXT NOT NULL
);
CREATE INDEX clips_camera ON clips(camera_id);
INSERT INTO cameras VALUES (1, 'porch'), (2, 'garage');
INSERT INTO clips(camera_id, path) VALUES
  (1, 'porch/0101.mp4'), (1, 'porch/0102.mp4'), (2, 'garage/0101.mp4');
""")
print("sqlite", sqlite3.sqlite_version)
print("foreign_keys =", con.execute("PRAGMA foreign_keys").fetchone()[0])

con.execute("DELETE FROM cameras WHERE id = 1")
con.execute("INSERT INTO clips(camera_id, path) VALUES (9, 'nowhere/0101.mp4')")
con.commit()
for row in con.execute("SELECT id, camera_id, path FROM clips"):
    print(row)

Output:

sqlite 3.37.2
foreign_keys = 0
(1, 1, 'porch/0101.mp4')
(2, 1, 'porch/0102.mp4')
(3, 2, 'garage/0101.mp4')
(4, 9, 'nowhere/0101.mp4')

The first line of SQLite's own answer is right there: foreign_keys = 0. SQLite's foreign key documentation says so directly: "Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection."

So the CREATE TABLE accepted the REFERENCES clause, stored it in the schema, and then the DELETE and the stray INSERT both ran as if it were a comment. The same page is explicit that the table definition is not where the decision happens: "A CREATE TABLE command operates the same whether or not foreign key constraints are enabled."

The cascade did not fire for the same reason. ON DELETE CASCADE is a foreign key action, and with enforcement off there is no foreign key processing at all.

Finding the orphans

check.py asks SQLite which rows break a declared constraint, then cross-checks with a plain join.

import sqlite3

con = sqlite3.connect("clips.db")
print("violations:")
for row in con.execute("PRAGMA foreign_key_check"):
    print(" ", row)
print("orphans by join:")
for row in con.execute("""
    SELECT clips.id, clips.camera_id, clips.path
    FROM clips LEFT JOIN cameras ON cameras.id = clips.camera_id
    WHERE cameras.id IS NULL"""):
    print(" ", row)

Output:

violations:
  ('clips', 1, 'cameras', 0)
  ('clips', 2, 'cameras', 0)
  ('clips', 4, 'cameras', 0)
orphans by join:
  (1, 1, 'porch/0101.mp4')
  (2, 1, 'porch/0102.mp4')
  (4, 9, 'nowhere/0101.mp4')

Each row from the pragma is the child table, the child row's rowid, the parent table and the constraint index. The PRAGMA reference describes it as: "The foreign_key_check pragma returns one row output for each foreign key violation."

The pragma works even when enforcement is off, which is what makes it useful as an audit: it reads the declared constraints and checks the data against them, whatever the current connection setting is. The join gives the same three rows. The pragma is the better habit because it covers every declared foreign key in the database without writing a join per table.

A second connection does not inherit the setting

A common fix is to add PRAGMA foreign_keys = ON in one place, such as a migration script or a maintenance tool, and assume the database is now protected. two_connections.py opens two connections on the same file and turns enforcement on in only one of them.

import sqlite3

a = sqlite3.connect("clips.db")
a.execute("PRAGMA foreign_keys = ON")
b = sqlite3.connect("clips.db")
print("a:", a.execute("PRAGMA foreign_keys").fetchone()[0])
print("b:", b.execute("PRAGMA foreign_keys").fetchone()[0])

b.execute("DELETE FROM cameras WHERE id = 2")
b.commit()
print("clips left for camera 2:",
      a.execute("SELECT count(*) FROM clips WHERE camera_id = 2").fetchone()[0])

Output:

a: 1
b: 0
clips left for camera 2: 1

Connection b deleted the garage camera and left its clip behind, while connection a sat right next to it with enforcement on. Running check.py again shows a fourth violation:

violations:
  ('clips', 1, 'cameras', 0)
  ('clips', 2, 'cameras', 0)
  ('clips', 3, 'cameras', 0)
  ('clips', 4, 'cameras', 0)
orphans by join:
  (1, 1, 'porch/0101.mp4')
  (2, 1, 'porch/0102.mp4')
  (3, 2, 'garage/0101.mp4')
  (4, 9, 'nowhere/0101.mp4')

The setting is not stored in the database file. Every process, every script and every tool that opens the file starts with whatever its own SQLite build defaults to, which for the build tested here was off. That includes the sqlite3 command-line shell someone uses for a quick manual fix.

Turning it on inside a transaction does nothing

pragma_in_txn.py inserts a row, then turns enforcement on, then reads the setting back.

import sqlite3

con = sqlite3.connect("txn.db")
con.execute("CREATE TABLE IF NOT EXISTS log(msg TEXT)")
con.execute("INSERT INTO log VALUES ('opened')")
print("in_transaction:", con.in_transaction)
con.execute("PRAGMA foreign_keys = ON")
print("after pragma inside txn:", con.execute("PRAGMA foreign_keys").fetchone()[0])

con.commit()
con.execute("PRAGMA foreign_keys = ON")
print("after commit:", con.execute("PRAGMA foreign_keys").fetchone()[0])

Output:

in_transaction: True
after pragma inside txn: 0
after commit: 1

The pragma ran without an error and the setting stayed at 0. The PRAGMA reference covers this case: "This pragma is a no-op within a transaction; foreign key constraint enforcement may only be enabled or disabled when there is no pending BEGIN or SAVEPOINT." The foreign key page adds the part that makes it hard to notice: "Attempting to do so does not return an error; it simply has no effect."

The script did not write BEGIN anywhere. In the configuration tested (Python 3.10, default connection settings) the sqlite3 module opened a transaction before the INSERT, and con.in_transaction reported True. Any code path that writes something before it sets the pragma can hit this, for example a helper that writes a log row first.

Once the transaction was committed, the same pragma took effect.

The fix: a connect() that checks

The pattern that held up in these tests is to set the pragma as the first statement on a fresh connection and then read it back, refusing to continue if it is not 1.

import sqlite3

def connect(path):
    con = sqlite3.connect(path)
    con.execute("PRAGMA foreign_keys = ON")
    if con.execute("PRAGMA foreign_keys").fetchone() != (1,):
        con.close()
        raise RuntimeError("foreign key enforcement is not on for " + path)
    return con

Reading it back matters. SQLite's documentation advises against assuming the default either way: "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." A build compiled without foreign key support would also show up here instead of failing silently later.

fixed.py builds the same index through the wrapper and repeats the two operations from the setup.

import sqlite3
from db import connect

con = connect("fixed.db")
con.executescript("""
CREATE TABLE cameras(id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE clips(
  id        INTEGER PRIMARY KEY,
  camera_id INTEGER NOT NULL REFERENCES cameras(id) ON DELETE CASCADE,
  path      TEXT NOT NULL
);
INSERT INTO cameras VALUES (1, 'porch'), (2, 'garage');
INSERT INTO clips(camera_id, path) VALUES
  (1, 'porch/0101.mp4'), (1, 'porch/0102.mp4'), (2, 'garage/0101.mp4');
""")
try:
    con.execute("INSERT INTO clips(camera_id, path) VALUES (9, 'nowhere/0101.mp4')")
except sqlite3.IntegrityError as e:
    print("insert refused:", e)

con.execute("DELETE FROM cameras WHERE id = 1")
con.commit()
for row in con.execute("SELECT id, camera_id, path FROM clips"):
    print(row)

Output:

insert refused: FOREIGN KEY constraint failed
(3, 2, 'garage/0101.mp4')

The stray insert is refused with IntegrityError, and deleting the porch camera removes both porch clips. Only the garage clip remains, which is what the schema claimed would happen.

Turning it on late is not a cleanup

The original clips.db still has four orphaned rows from the earlier scripts. late_switch.py opens it through the same wrapper.

from db import connect

con = connect("clips.db")
print("enforcement:", con.execute("PRAGMA foreign_keys").fetchone()[0])
print("violations still present:", len(con.execute("PRAGMA foreign_key_check").fetchall()))

con.execute("UPDATE clips SET path = path WHERE camera_id = 9")
con.commit()
print("update of an orphan row: allowed")

con.execute("""DELETE FROM clips
               WHERE camera_id NOT IN (SELECT id FROM cameras)""")
con.commit()
print("violations after cleanup:", len(con.execute("PRAGMA foreign_key_check").fetchall()))

Output:

enforcement: 1
violations still present: 4
update of an orphan row: allowed
violations after cleanup: 0

Opening the file with enforcement on did not raise an error and did not touch the orphans. An UPDATE that leaves the camera_id column alone was allowed on an orphaned row. The violations disappeared only when a DELETE removed them. Deleting is one choice; for a clip index you might instead insert a placeholder camera, or move the rows to a table for review. The point is that the pragma will not make that choice for you, and an index that ran without enforcement for a while needs a foreign_key_check pass before you trust it.

Checklist

• Put PRAGMA foreign_keys = ON in one connect() helper, as its first statement, and read the value back.
• Route every script and tool through that helper, including one-off maintenance scripts.
• Run PRAGMA foreign_key_check on any existing database before switching enforcement on, and decide what to do with what it finds.
• Treat a manual session in the sqlite3 shell as unprotected unless you type the pragma yourself.
• Add an index on the child key column (clips.camera_id here). SQLite's foreign key page puts it this way: "Indices are not required for child key columns but they are almost always beneficial."

Things I did not test

• Other SQLite builds and wrappers. Only the SQLite library linked into Python 3.10.12 on this machine was tested. Other builds can be compiled with a different default, and ORMs or drivers may set the pragma for you or may not.
• Newer Python transaction modes. Python versions after 3.10 added other ways to control transactions in the sqlite3 module. I did not test whether they change when a transaction is opened.
• Concurrency. The two connections ran one after another in one process. Nothing here measures what happens with several writers at once.

Sources. Quotes are from SQLite's foreign key documentation and the PRAGMA reference. Test results come from the scripts above, run once each in the order shown in one folder, with the output pasted unedited.

If you want an old phone to record clips in the background so there is a folder worth indexing, the app I make is Background Camera RemoteStream on Google Play. Nothing above depends on it.

The schema states the rule once. Each connection decides, every time it opens the file, whether the rule applies.