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.…