SQLite Basics: See Which Changes Survive a Constraint Error

A failed insert does not always undo the same amount of work. SQLite’s ABORT, FAIL, and ROLLBACK policies have different boundaries. A small import with one duplicate name makes those boundaries visible before you choose an application error-handling policy.

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. All three SQL files below were executed through the Python runner, and the output shown is observed output. The standalone SQLite CLI is not used.

Prepare a scratch directory

You need Bash, Python 3.11 or later with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages or SQLite CLI are needed. Keep the same terminal open and save files as UTF-8. Stop if setup fails before saving any example files.

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

Save the following as run-sql.py. This educational runner deliberately catches exactly one UNIQUE-constraint failure per script so that later SQL can inspect the surviving rows. It re-raises other errors and fails if the expected error never happens or happens more than once. Do not use this continue-after-error policy for a production import.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
expected_errors = 0
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            try:
                cursor = connection.execute(statement)
                for row in cursor:
                    print("|".join("NULL" if value is None else str(value) for value in row))
            except sqlite3.IntegrityError as error:
                if error.sqlite_errorname != "SQLITE_CONSTRAINT_UNIQUE":
                    raise
                expected_errors += 1
                print("expected " + error.sqlite_errorname)
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
    if expected_errors != 1:
        raise AssertionError("Expected exactly one UNIQUE failure")
finally:
    connection.close()

Each invocation starts with an independent in-memory database. isolation_level=None leaves transaction boundaries to the SQL. Rows are printed with pipe separators, without escaping embedded separators or newlines. Put each statement’s terminating semicolon at the end of a line and do not put two statements on one line.

1. Undo the failing statement with ABORT

Save as abort-import-statement.sql:

CREATE TABLE labels (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);
INSERT INTO labels VALUES (1, 'kept');
BEGIN;
INSERT INTO labels VALUES (2, 'before');
INSERT OR ABORT INTO labels VALUES
    (3, 'first'), (4, 'kept'), (5, 'last');
SELECT id, name FROM labels ORDER BY id;
COMMIT;
SELECT 'committed rows', COUNT(*) FROM labels;

Run:

python3 run-sql.py abort-import-statement.sql

Observed output:

expected SQLITE_CONSTRAINT_UNIQUE
1|kept
2|before
committed rows|2

The duplicate name is in the middle of a three-row insert. ABORT removes that statement’s earlier first row too, but preserves before, which came from a separate statement. The transaction remains open and can be committed. ABORT is the default conflict policy when no alternative is specified. SQLite conflict resolution.

The COMMIT is intentional for this demonstration. A real all-or-nothing import would normally roll back after a rejected input instead of accepting the partial import.

2. Preserve earlier rows from the statement with FAIL

Save as fail-import-statement.sql:

CREATE TABLE labels (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);
INSERT INTO labels VALUES (1, 'kept');
BEGIN;
INSERT INTO labels VALUES (2, 'before');
INSERT OR FAIL INTO labels VALUES
    (3, 'first'), (4, 'kept'), (5, 'last');
SELECT id, name FROM labels ORDER BY id;
COMMIT;
SELECT 'committed rows', COUNT(*) FROM labels;

Run:

python3 run-sql.py fail-import-statement.sql

Observed output:

expected SQLITE_CONSTRAINT_UNIQUE
1|kept
2|before
3|first
committed rows|3

Only the policy changed, but first now survives. FAIL stops the failing statement without undoing changes it already made. The later last row is never inserted in this example. FAIL policy.

This matters when retrying an operation: retrying the whole input without checking the surviving state can encounter another duplicate. An error response alone is not evidence that zero rows changed. These scripts use a listed VALUES sequence; do not infer a guaranteed row-processing order for an arbitrary UPDATE.

3. Undo the surrounding transaction with ROLLBACK

Save as rollback-import-transaction.sql:

CREATE TABLE labels (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);
INSERT INTO labels VALUES (1, 'kept');
BEGIN;
INSERT INTO labels VALUES (2, 'before');
INSERT OR ROLLBACK INTO labels VALUES
    (3, 'first'), (4, 'kept'), (5, 'last');
SELECT id, name FROM labels ORDER BY id;
SELECT 'remaining rows', COUNT(*) FROM labels;

Run:

python3 run-sql.py rollback-import-transaction.sql

Observed output:

expected SQLITE_CONSTRAINT_UNIQUE
1|kept
remaining rows|1

ROLLBACK removes the earlier before insert as well as the failing statement. The initial kept row remains because it was inserted before BEGIN. There is no COMMIT at the end: this conflict already ended the transaction. ROLLBACK policy.

The printed error name is the same in all three examples. The selected policy and transaction state explain the different surviving data. The runner relies on Python’s SQLite exception attributes, which were added in Python 3.11. Python SQLite exceptions.

These examples exercise UNIQUE failures only. In particular, a foreign-key error does not acquire FAIL’s partial-statement behavior. Handle application failures explicitly, and use a deliberate transaction or savepoint boundary for the unit of work you need to undo. SQLite conflict exceptions, transaction reference.

Clean up

In the same terminal, remove only the named example files and the empty scratch directory:

rm -- run-sql.py abort-import-statement.sql fail-import-statement.sql rollback-import-transaction.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.