Skip to content
SQLite: Preserve child rows when updating a parent

SQLite: Preserve child rows when updating a parent

November 16, 2026

INSERT OR REPLACE can remove an account’s notes even when the replacement uses the same account ID. A uniqueness conflict can delete the existing parent before the new row is inserted.

Set up and run

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and the standard mktemp, rm and rmdir utilities. No additional packages or SQLite CLI are needed. Keep the same terminal open through cleanup. Stop if setup fails before saving the source.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

Save as replace-parent.py:

import sqlite3


def demonstrate(mode):
    db = sqlite3.connect(":memory:", isolation_level=None)
    try:
        db.execute("PRAGMA foreign_keys = ON")
        assert db.execute("PRAGMA foreign_keys").fetchone() == (1,)
        db.execute("PRAGMA recursive_triggers = OFF")
        db.executescript("""
            CREATE TABLE accounts (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
            CREATE TABLE notes (
                id INTEGER PRIMARY KEY,
                account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
                body TEXT NOT NULL
            );
            INSERT INTO accounts VALUES (1, 'Original');
            INSERT INTO notes VALUES (10, 1, 'Keep this note');
        """)
        if mode == "REPLACE":
            db.execute("INSERT OR REPLACE INTO accounts VALUES (1, 'Renamed')")
        else:
            db.execute("""INSERT INTO accounts VALUES (1, 'Renamed')
                ON CONFLICT(id) DO UPDATE SET name = excluded.name""")
        parent = db.execute("SELECT id, name FROM accounts").fetchall()
        children = db.execute("SELECT id, body FROM notes ORDER BY id").fetchall()
        assert parent == [(1, "Renamed")]
        assert children == ([] if mode == "REPLACE" else [(10, "Keep this note")])
        print(mode + " parent:", parent)
        print(mode + " notes:", children)
    finally:
        db.close()


demonstrate("REPLACE")
demonstrate("UPSERT")

Run:

python3 replace-parent.py

Observed output:

REPLACE parent: [(1, 'Renamed')]
REPLACE notes: []
UPSERT parent: [(1, 'Renamed')]
UPSERT notes: [(10, 'Keep this note')]

Compare the child rows

Both runs finish with the same account ID and renamed account. The REPLACE run has no notes left. Its primary-key conflict deletes the old parent, and the declared ON DELETE CASCADE removes the note. Inserting another parent with ID 1 does not recreate that note.

The UPSERT run changes the name on the existing account, leaving its ID and note intact. Its conflict target is the primary key, and its DO UPDATE clause assigns only the name from the attempted insert. SQLite documents these separately as REPLACE conflict handling and UPSERT.

The connections explicitly enable and verify foreign-key enforcement before creating the tables. Recursive triggers are disabled, but foreign-key actions still run. SQLite’s foreign-key documentation distinguishes these actions from SQL triggers. Do not disable foreign keys to avoid the cascade; that can permit inconsistent data.

Decide whether replacement is intentional

Use an update when an existing entity should retain its children. Review every column assigned by the UPSERT: changing a referenced key or invoking application-defined triggers can have additional effects. This example keeps the key unchanged and defines no application triggers.

REPLACE can be appropriate when deletion and reinsertion are intended. Check every uniqueness constraint that might conflict, omitted-column defaults, and related foreign-key actions before using it. The example demonstrates a primary-key conflict with CASCADE; other schemas can reject the operation or behave differently.

Test this behavior in a disposable database before changing a production import. Both demonstrations here create a fresh in-memory database, so the deliberate deletion cannot affect existing files. UPSERT requires SQLite 3.24.0 or later.

Clean up

In the same terminal, remove only the example file and the empty scratch directory. If the directory is not empty, inspect what remains before deleting anything else.

rm -- replace-parent.py
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.

Last updated on