ON CONFLICT Algorithms and Constraint-Failure Semantics

Learning outcomes

Constraint failure is not only “error or no error.” SQLite has legacy conflict-resolution algorithms that determine how much work survives a violation. To use them safely, you must distinguish statement state from transaction state and understand that REPLACE may delete a different row rather than update it.

Two SQLite features use the phrase ON CONFLICT

This lesson covers SQLite’s older conflict-resolution mechanism: ROLLBACK, ABORT, FAIL, IGNORE, and REPLACE. In INSERT and UPDATE syntax, the algorithm appears as OR, for example INSERT OR IGNORE. Chapter 6 later covers UPSERT syntax such as ON CONFLICT(key) DO UPDATE. They are related only by name and problem domain; do not treat them as interchangeable syntax.

-- Legacy conflict algorithm covered here:INSERT OR IGNORE INTO device(code) VALUES ('PUMP-007');-- UPSERT form deferred to Chapter 6:-- INSERT INTO device(code, label) VALUES (...)-- ON CONFLICT(code) DO UPDATE SET ...;

The five algorithms answer “what survives?”

ABORT is SQLite’s default algorithm. The contrast between ABORT and FAIL is especially important for a multi-row UPDATE or INSERT: FAIL can leave a partially applied statement.

Constraint classes: read current SQLite behavior precisely

A conflict clause may be declared on UNIQUE, NOT NULL, and PRIMARY KEY constraints. A schema-level conflict clause is not attached to CHECK or FOREIGN KEY constraints. Statement-level OR algorithms can affect CHECK handling in their documented ways, but foreign-key violations use ABORT-like behavior rather than being silently ignored or replaced.

ABORT versus FAIL: same error, different stored state

Use a disposable table where the third logical value conflicts with a UNIQUE constraint. With ABORT, the entire multi-row statement is undone. With FAIL, rows changed before the conflict remain.

DROP TABLE IF EXISTS conflict_demo;CREATE TABLE conflict_demo (    id INTEGER PRIMARY KEY,    code TEXT NOT NULL UNIQUE);INSERT INTO conflict_demo VALUES (1, 'EXISTING');-- Predict first. Default/OR ABORT:INSERT OR ABORT INTO conflict_demo(id, code) VALUES    (2, 'A'),    (3, 'EXISTING'),    (4, 'C');-- Statement errors; row 2 is also backed out.SELECT id, code FROM conflict_demo ORDER BY id;-- Reset, then test FAIL:DELETE FROM conflict_demo WHERE id > 1;INSERT OR FAIL INTO conflict_demo(id, code) VALUES    (2, 'A'),    (3, 'EXISTING'),    (4, 'C');-- Statement errors; row 2 remains, row 4 was never attempted.SELECT id, code FROM conflict_demo ORDER BY id;

This is why FAIL is dangerous when callers assume “an exception means the statement did nothing.” The correct recovery logic must know the algorithm and inspect/rollback as needed.

ROLLBACK reaches farther than ABORT

ABORT preserves successful statements that happened earlier in the explicit transaction. ROLLBACK removes them too.

DELETE FROM conflict_demo;INSERT INTO conflict_demo VALUES (1, 'EXISTING');BEGIN;INSERT INTO conflict_demo VALUES (10, 'EARLIER-STATEMENT');INSERT OR ROLLBACK INTO conflict_demo(id, code) VALUES    (11, 'OK'),    (12, 'EXISTING');-- Error. The explicit transaction has been rolled back.SELECT id, code FROM conflict_demo ORDER BY id;

The durable result is only the pre-transaction row (1, EXISTING). Use ROLLBACK deliberately; do not choose it merely because it sounds “safer.” It changes the scope of lost work.

IGNORE skips data; that can be exactly the bug

INSERT OR IGNORE is useful only when the skipped condition is understood and intentionally acceptable. It is a poor generic import strategy because “no error” can conceal dropped rows.

DELETE FROM conflict_demo;INSERT INTO conflict_demo VALUES (1, 'EXISTING');INSERT OR IGNORE INTO conflict_demo(id, code) VALUES    (2, 'A'),    (3, 'EXISTING'),    (4, 'C');SELECT id, code FROM conflict_demo ORDER BY id;SELECT changes() AS rows_changed_by_last_statement;

The final table contains ids 1, 2, and 4. If id 3 represented a required business event, silently skipping it would be data loss. Prefer staging plus explicit validation for uncertain imports.

REPLACE is not UPDATE: prove the delete/insert consequence

For a UNIQUE/PRIMARY KEY conflict, REPLACE removes the row that blocks the new row, then performs the write. If that removed row is a parent with cascading children, those child lifecycle actions can run. This is very different from updating the existing row in place.

PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS replace_note;DROP TABLE IF EXISTS replace_device;CREATE TABLE replace_device (    device_id INTEGER PRIMARY KEY,    code TEXT NOT NULL UNIQUE,    label TEXT NOT NULL);CREATE TABLE replace_note (    note_id INTEGER PRIMARY KEY,    device_id INTEGER NOT NULL      REFERENCES replace_device(device_id) ON DELETE CASCADE,    text TEXT NOT NULL);INSERT INTO replace_device VALUES (1, 'PUMP-007', 'Old row');INSERT INTO replace_note VALUES (100, 1, 'History attached to id 1');INSERT OR REPLACE INTO replace_device(device_id, code, label)VALUES (2, 'PUMP-007', 'Replacement row');SELECT * FROM replace_device;SELECT * FROM replace_note;

The final device uses id 2. The note can disappear because replacing the conflicting code removed parent id 1, activating ON DELETE CASCADE, then inserted a new row. If you intended to modify a row while preserving identity and children, REPLACE was the wrong tool.

Prediction lab: record the state before running each case

For each block, write your predicted final ids first. Run the blocks independently after resetting the table. The lesson is not memorizing names—it is learning to reason from statement and transaction scope.

DROP TABLE IF EXISTS algo_lab;CREATE TABLE algo_lab (    id INTEGER PRIMARY KEY,    code TEXT NOT NULL UNIQUE);INSERT INTO algo_lab VALUES (1, 'X');-- Case A: ABORTINSERT OR ABORT INTO algo_lab VALUES (2,'A'),(3,'X'),(4,'C');-- Predict final ids: ?-- Reset to only (1,'X') before each next case.-- Case B: FAILINSERT OR FAIL INTO algo_lab VALUES (2,'A'),(3,'X'),(4,'C');-- Predict final ids: ?-- Case C: IGNOREINSERT OR IGNORE INTO algo_lab VALUES (2,'A'),(3,'X'),(4,'C');-- Predict final ids: ?

Expected states are: ABORT → only id 1; FAIL → ids 1 and 2; IGNORE → ids 1, 2, and 4. The SQL error for ABORT/FAIL is not enough information by itself to infer the resulting state.

Summary and bridge

Conflict algorithms define failure scope, not just error formatting. ABORT undoes the failing statement, FAIL may leave a partial statement, ROLLBACK can erase prior transaction work, IGNORE deliberately skips rows, and REPLACE can delete existing rows. Lesson 5 returns to proactive integrity design: generated columns, cross-column rules, and audit PRAGMAs that tell you what kind of integrity has actually been checked.

Authoritative references

• SQLite — The ON CONFLICT Clause.
• SQLite — CREATE TABLE constraint enforcement.
• SQLite — UPSERT (for contrast; Chapter 6).
• SQLite — Foreign Keys.