Why can INSERT OR REPLACE delete related data in SQLite?

REPLACE is not simply an in-place update. On certain constraint conflicts it deletes the conflicting row before inserting the replacement, which can interact with foreign keys and triggers. Use a supported UPSERT with an explicit conflict target and update clause when preserving row identity is intended. Test the parent’s ID, child references and trigger effects during a conflict. Do not assume AUTOINCREMENT makes replacement semantics harmless. Review which columns should retain their old values and which should change; an omitted column in a replacement can revert to a default rather than preserve existing data. Minimal example:

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")
db.executescript("""
CREATE TABLE parent(id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE child(parent_id INTEGER REFERENCES parent(id) ON DELETE CASCADE);
INSERT INTO parent VALUES (1, 'old');
INSERT INTO child VALUES (1);
INSERT OR REPLACE INTO parent VALUES (1, 'new');
""")
assert db.execute("SELECT COUNT(*) FROM child").fetchone()[0] == 0
db.close()
# REPLACE deleted the old parent, triggering the cascade.