Why Deleted Images Leave Tags Behind: Enable SQLite Foreign Keys on Every Connection
Reproduce orphaned image tags in SQLite, enable foreign keys per connection, and verify the cascade and earlier violations with local tests.

The `images` row for a vase is gone. A query of `image_tags` still returns `blue`, attached to image 1. The application can no longer open that image from its catalog, yet the tag still contributes to any count taken directly from the association table. An inner join from tags back to images can hide the disagreement on one screen and leave it visible in another. The first useful question is what the two tables contain immediately after the delete.
The small catalog in this article makes that disagreement reproducible. It uses an original Python fixture with one image and one tag. Both versions create exactly the same tables. The connection with enforcement turned off leaves one tag after deletion. With enforcement on, the same statement leaves zero. The two numbers came from local runs in Python 3.12.3 with SQLite 3.45.1. The retained output also records a pragma reading of 1 for the enabled connection. You can use those three observations to tell whether a similar bug is in the schema, the connection setup, or the operation that actually performed the delete.
Read the orphan directly from the catalog
Start with a query that addresses the child table itself. In the fixture, `image_tags.image_id` is the value to follow. An image is present in `images` only while a row with that `id` exists. For diagnosis, read the parent row and then look for children with that same identifier. An empty parent result alongside one or more children is the condition you want to see clearly. A test that only opens the image by its identifier can pass even when every tag belonging to the image survives.
Consider a project with a gallery page, a filter for blue images, and a sidebar showing the number of images with each label. The gallery might join against `images`, while the sidebar might count `image_tags` directly. You can rewrite the sidebar to join too, but that changes what the query counts. It does not resolve whether the child rows belong in the database. Decide whether a tag in this table means “this image currently has this label.” If that is the contract, a child whose image is missing is bad data regardless of which screen exposes it.
The original reproduction isolates the problem from the rest of the project. The row `(1, 'vase')` is the parent. The row `(1, 'blue')` is the child. The test deletes parent 1 using `DELETE FROM images WHERE id=1` and reads `count(*)` from the child table. The fixture has only one tag at that point, so the aggregate is enough to distinguish a surviving association from an empty table. When adapting the query to a catalog with several images, filter by the affected identifier or inspect the exact surviving rows. Otherwise, a nonzero count may come from a different image and tell you nothing about the one you deleted.
There is a second design decision hiding in the word “tag.” The example stores the text `blue` on each association. In a richer catalog, a vocabulary table might store the label once and a linking table might associate it with many images. Removing an image can remove its links while leaving the vocabulary intact. That design would put the cascade on the foreign key from the linking table to the image table. The fixture has no separate vocabulary table. It also has no file storage operation. Its delete concerns a catalog record and its associations, so application code can give those actions precise names.
Trace the schema before changing the connection
The schema declares `images.id` as the primary key. The child has an `image_id` integer column, marked `NOT NULL`, and a text tag. Its foreign key references `images(id)` and declares `ON DELETE CASCADE`. Read those parts together. The target of the reference identifies the row that owns the association. The non-null column means every association in this particular design must name an image. The delete action states what should happen to its associations when the owning row disappears.
Check the schema of the actual file your application opened. A creation script from a new branch tells you what a newly initialized catalog would look like. A catalog created earlier could have an older table definition. Opening such a file through a helper that enables enforcement cannot create a foreign key that the table never declared. During an investigation, inspect the foreign keys of the live table with a read operation such as `PRAGMA foreign_key_list(image_tags)`, then compare the referenced columns and delete action with the project schema. Work on a disposable copy if you need to run a deletion to confirm behavior.
The cascade also expresses an ownership choice. Imagine an annotation table in which a reviewer needs to read old decisions after the original image has been withdrawn. Automatically deleting those decisions would make it harder to reconstruct the review. The appropriate action would depend on how that project retains history and how an annotation identifies its source. The image tag in this exercise has no historical role. It exists to label a current image, so removal with that image is consistent with the row's purpose. Define that role before adapting a cascade to a different child table.
Notice what the schema deliberately leaves open. There is no uniqueness rule for the pair of `image_id` and `tag`, so the presence of a valid parent does not by itself prevent duplicate associations. There is also no vocabulary constraint that would make the label `blue` valid and some other string invalid. These are separate rules you may choose to add. The sample tests isolate one relationship, which makes their outcomes easy to read. If a project needs uniqueness or a controlled vocabulary, give each rule its own fixture and assertion so a failure points to the specific contract that was broken.
Initialize the connection before it begins work
`open_catalog` opens the supplied path, asks for foreign-key enforcement, and immediately reads back the setting. The function expects one result row containing 1. If that expectation fails, it closes the connection and raises `RuntimeError`. The calling code receives either a connection that reported the requested setting or an error before it can write catalog data. The helper does not create tables. Keeping connection setup separate from initialization lets the same helper open both a new fixture and a file that already contains its catalog.
`initialize` runs the fixture schema, inserts the vase and its tag using parameters for the text values, and commits. Run that initialization only against a fresh database you own. It creates tables and inserts a fixed identifier, so it is not intended as an operation to run every time an application starts. The enabled and disabled cases each get a fresh in-memory catalog. That prevents the result from depending on whichever fixture happened to run first.
The ordering of the pragma and the first write matters. One test starts with enforcement off, explicitly begins a transaction, and asks to enable enforcement while that transaction is open. The readback remains 0. It rolls the transaction back, issues the same pragma, and obtains 1. A statement can be accepted without producing the state change the caller wanted. This is why the helper reads the setting back instead of treating the absence of an exception as proof.
Foreign-key enforcement is configured per connection and cannot be toggled in the middle of a multi-statement transaction. SQLite foreign-key documentation
When adapting the helper, follow the acquisition path all the way to the code that will use the returned connection. For example, a batch tag importer might open its own connection even if the web application uses a shared database module. Setting the pragma in the web application's startup routine gives you evidence about the connection used in that routine. The importer needs the same initialization on the connection it uses. A connection pool adds a similar distinction between configuring a new connection and borrowing an existing one. Put the initialization where connections are created and decide how the pool verifies the setting when it hands them out.
If your application wraps connections in a library that begins transactions automatically, find the point where the wrapper allows setup before a transaction starts. A successful readback of 1 there is a more useful check than reading the setting through a separate interactive session. If the readback gives 0, record which connection was checked and whether a transaction was active. The local test changes only those conditions and shows why simply moving the same SQL statement earlier can matter.
catalog_fk.py
import sqlite3
SCHEMA="""CREATE TABLE images(id INTEGER PRIMARY KEY,name TEXT NOT NULL);
CREATE TABLE image_tags(image_id INTEGER NOT NULL,tag TEXT NOT NULL,
FOREIGN KEY(image_id) REFERENCES images(id) ON DELETE CASCADE);"""
def open_catalog(path):
db=sqlite3.connect(path)
db.execute('PRAGMA foreign_keys=ON')
if db.execute('PRAGMA foreign_keys').fetchone()!=(1,):
db.close();raise RuntimeError('Foreign-key enforcement unavailable')
return db
def initialize(db):
db.executescript(SCHEMA)
db.execute('INSERT INTO images VALUES(1,?)',('vase',))
db.execute('INSERT INTO image_tags VALUES(1,?)',('blue',))
db.commit()
Recorded local check: Nine SQLite methods on memory and one owned temporary file, Python/SQLite actualversions recorded. ExplicitOFF baseline orphans, ONcascade, invalidchildrejection, perconnection settings, ONinsideactive tx ineffective, preexistingorphan remains/foreign_key_checkdetects, separateparentpreserved,reopenhelper. No production DB deletion/migration, simultaneouswriters, distributed sync, performance or allschema constraint coverage.; environment: Python 3.12.3 on Windows; SQLite 3.45.1
Use the tests to locate the failing boundary
The baseline and cascade tests form a pair. `test_baseline_disabled_leaves_tag` sets enforcement off explicitly and observes a surviving child. `test_enabled_cascade_removes_tag` uses the helper and observes none. Both use the original schema and initial rows. This makes them evidence for the effect of the connection setting in the controlled setup. A failing comparison in an adapted test is easier to diagnose if you keep the schema, starting rows, and delete operation identical between its cases.
`test_orphan_insert_rejected` looks at the relationship from the other direction. It tries to attach a tag to image 9 when the fixture contains only image 1, and expects `sqlite3.IntegrityError`. An application can use a friendly message when an image is missing, but the database can still reject a write when a caller hands it an invalid identifier. In a similar test, isolate the statement that should fail. If the fixture also violates a unique or non-null constraint, catching the exception class alone may give false confidence about which rule fired.
`test_new_connection_has_enforcement` checks the helper's normal result directly. `test_connection_settings_are_separate` opens two independent in-memory connections and reads 1 from the enabled one and 0 from the disabled one. The test demonstrates distinct settings on the two connections. It does not claim that two writers can make simultaneous changes without locking. The file-reopen test covers another question. It initializes a temporary catalog, closes the first connection, and opens the same file through the helper. The second connection reads both the stored name `vase` and a setting of 1. The name is persisted data. The helper requests enforcement again for the reopened connection.
`test_other_parent_keeps_its_tag` gives the cascade a boundary to respect. It adds image 2, `cup`, with tag `red`. After image 1 is deleted, the selected child rows are exactly `[(2, 'red')]`. A test that only checked for the absence of `blue` could also pass if a faulty cleanup deleted every row. Checking the entire remaining result makes the intended scope visible. Apply the same idea to catalog changes by including a neighbor whose association must survive.
The transaction and existing-orphan tests bring the total to nine local test methods. The first isolates the moment enforcement can be switched. The second tests whether enabling it will change rows already committed under the earlier connection policy. The suite runs with Python's `unittest` discovery pointed at the directory containing the fixture and its test module. Keep that directory disposable, especially for the file-reopen test. For a project test, include the runtime SQLite version in the record so someone reproducing a difference can see which library answered the queries.
The baseline and enabled queries read their child counts on the connection that performed the delete. That is a useful early diagnostic even before you add application-level commit handling. To extend the test through a real write path, execute that path against a disposable file, complete the operation's normal commit, and read the expected parent and child rows through a separate connection. This proposed integration check asks whether the app actually persists the change. It complements the local suite's checks of cascade behavior and file reopening.
Inspect old violations before planning a cleanup
The existing-orphan test starts with enforcement off and deletes the only parent. It commits the change, then enables enforcement. The child count is still 1. A subsequent `PRAGMA foreign_key_check` produces one reported violation. Both observations matter. The readback of the setting describes what is enabled for the connection, while the violation check describes a relationship in the stored catalog. A project can have the desired connection setup and still need to account for data written before the change.
Suppose an older importer used a catalog as a temporary scratchpad and loaded associations before it loaded all the images. Some old associations might refer to images that later arrived, while others could have come from an interrupted import. A useful first review separates the reported rows by source if the catalog has an import identifier or a log that can supply one. For each orphan, compare the stored identifier with the available source records. The right outcome may be to recover a missing parent, correct a mistaken identifier, or remove a link after confirming it has no owner. The fixture uses one deliberately abandoned tag to demonstrate detection. It supplies no policy for deciding which data a real project should keep.
Before testing a repair, preserve a consistent copy of the catalog and any external information needed to interpret those identifiers. Perform proposed changes on the copy. Record the starting violation count, the identifiers you decided to correct, and the results of a fresh check afterward. Also query a few rows that should survive. That comparison catches unintended broad deletes, which a clean integrity report alone would miss. An empty list of violations is compatible with a database from which too much data was removed.
Check the project schema and its application expectations before treating every reported relationship as the same kind of cleanup. An image may have tags, an import record, thumbnails, and review notes. Each child table may have its own deletion rule and its own reason for retaining data. An inventory of affected rows is easier to review if it records which relation each item belongs to. Once you can explain the choice for each relationship, you can write an integration test using a few representative records. The fixture's two-parent test offers a small pattern for checking that a correction touches only its intended associations.
Distinguish a wrong database file from a wrong setting
A successful helper can still open the wrong catalog. The `path` argument comes from its caller. Two commands launched from different directories may supply the same relative filename and reach different files. In that case, one command can report enforcement enabled and read the correct schema while the application keeps using an older catalog. Before changing SQL, compare the actual paths selected by both callers. Log the resolved path where appropriate for local debugging, or compare a project-specific identifier in a controlled database if paths should stay private. The file-reopen test holds the path constant inside a temporary directory so its two connections refer to the same data.
Keep a distinction between a missing parent in the database and an inaccessible image in the gallery. A catalog row may exist and refer to a file whose location is stale. The relationship between that row and its tag is still valid as far as SQLite is concerned. Conversely, an image file can remain on disk after its catalog row has gone. Start with the catalog query when testing this foreign key. Then follow the project's separate record of where the image bytes live if the symptom includes a broken preview. This distinction helps prevent an attempt to clean up tags when the reported failure actually comes from asset storage.
If a catalog has a separate process that synchronizes files into rows, examine the order in which it assigns identifiers. A folder scan may yield names, and a later step may assign database IDs. The tag writer should use the ID that belongs to the committed image record or take part in the same unit of work that creates it. A guessed ID derived from a position in the folder list can refer to a different existing image and still satisfy the foreign key. To test the application's assignment rule, use two images with distinct tags and verify the exact pairings after import. The existing fixture's `red` association illustrates why checking membership can be more informative than counting rows alone.
Carry the connection rule through each write path
List the paths that create connections for catalog writes. A web request handler, a repair command, a scheduled importer, and a development tool may all use the same file. Route each authorized write path through the catalog's connection setup where possible. If a third-party framework owns connection creation, use its initialization hook for the setting and verify the value at that boundary. Give the verification failure a clear error so a job does not report success after writing through a connection whose setup could not be confirmed.
Keep catalog deletion separate from deleting bytes on disk. In a project where several catalog entries can point to the same image file, the removal of one entry may leave another reference that still needs those bytes. Even when every row has its own file, disk operations and database commits have different failure points. Write down which component owns each action and what a retry would observe. For example, an app could first record an intent to retire a catalog entry, commit the corresponding database work, and then give a separate cleanup process the file identifier to inspect. That is a design to test in the owning project. The reproduced cascade proves the row behavior within the catalog only.
Consider a concurrent editor viewing an older revision of an image record. It can submit a stale edit even when every foreign key is valid. The catalog needs an additional rule for accepting edits against the expected revision. An expected revision addresses another contract: guard a catalog update with an expected revision when stale edits matter. Referential integrity addresses which rows may depend on other rows.
An application-level regression can bring the catalog decisions together without recreating every local unit test. Prepare a file with two image rows and tags for both, invoke the exact delete entry point used by the application, and check that the write committed. Read back the affected image, its tags, and the second image's tags. Then invoke the normal tag entry point with the deleted identifier and verify how the application reports the rejection. This proposed test covers how the caller handles the database result and what an operator sees. It is different from calling the fixture directly.
When another orphan appears, capture the catalog path, the foreign key declared for the relevant child table, and the setting read from the connection that executed the write. Include the affected identifier and enough detail to tell whether the orphan predated the run. These observations give the next maintainer something specific to reproduce. The most useful comparison is between the row state you expected at the end of the operation and the row state the database actually reports.
Prepared with AI assistance and automated review. The original code was tested locally. The lead photograph illustrates the subject.



