Use a CHECK constraint for allowed SQLite queue states

By Batu · English technical notes

Also published in our Blogger archive.

Restrict the persisted state vocabulary

The CHECK expression allows queued, claimed, and done; invented raises IntegrityError while the valid row remains. Pair CHECK with NOT NULL when every row must hold one listed word, since SQLite considers a NULL CHECK result satisfied.

This validates values, not state transitions. A separate conditional update is needed if done must never become queued again.

Example

import sqlite3
from pathlib import Path
from tempfile import TemporaryDirectory

with TemporaryDirectory() as directory:
    connection = sqlite3.connect(Path(directory) / "queue.db")
    connection.execute("CREATE TABLE job(state TEXT CHECK(state IN ('queued', 'claimed', 'done')))")
    connection.execute("INSERT INTO job VALUES ('queued')")
    try:
        connection.execute("INSERT INTO job VALUES ('invented')")
    except sqlite3.IntegrityError:
        rejected = True
    else:
        rejected = False
    assert rejected
    assert connection.execute("SELECT state FROM job").fetchone()[0] == "queued"
    print("invalid-state=rejected")

Expected stdout:

invalid-state=rejected

Sources

- SQLite CHECK constraints

Prepared with AI assistance. The example uses synthetic data; its stated limits apply.