hesela.devSQLite REPLACE
SQLite REPLACE is a conflict-resolution algorithm that deletes rows conflicting with a UNIQUE or PRIMARY KEY constraint before completing the write.
REPLACE is not an in-place update. Its implicit conflict deletions invoke delete triggers only when recursive_triggers is enabled; foreign-key actions follow separate rules. [1] [2]
In a SQLite 3.45.1 fixture, replacing parent ID 1 deleted both dependent rows under ON DELETE CASCADE, with recursive triggers either off or on. The final parent existed and both checks passed. A conflict-targeted UPSERT that changed only the label retained the children. [4] [5]
Changing a referenced key or running other triggers can change an UPSERT's effects; not every UPSERT preserves dependents. CHECK or foreign-key violations are not repaired by REPLACE. [1] [3]
One incoming row can conflict with different existing rows through different unique keys. In twelve additional fixtures, incoming (1, beta) displaced parent 1 and parent 2, removing both sets of children. A label-only ON CONFLICT(id) update retained them; assigning the already-used code beta in DO UPDATE raised a UNIQUE error instead. The complete rows, settings and rollback controls are recorded. [6]
Relations
Citations
- SQLite ON CONFLICT: REPLACE rules and exceptions
- SQLite Foreign Key Support: actions and recursion limits
- SQLite UPSERT: conflict targets and DO UPDATE
- Hesela: REPLACE, foreign-key actions and complete reopened rows
- Hesela: 24 matrix cases, four global-deferral controls and two orphan controls
- Hesela: twelve two-unique-key write fixtures, complete rows and rollback trials
Sources & caveats
- Controlled software observations on SQLite 3.45.1; one INTEGER PRIMARY KEY conflict, single writer, DELETE and WAL. Not a crash test or failure-rate estimate.
- Two-key supplement: one INTEGER PRIMARY KEY and one BINARY-collated TEXT UNIQUE constraint; ON DELETE CASCADE, schema deferral on, global deferral off. Original single-key observations retained separately.