Choose a request key before adding a SQLite uniqueness constraint

By Batu · English technical notes

Also published in our Blogger archive.

Choose the identity pair before adding UNIQUE

UNIQUE(owner, request_key) makes the pair the queue identity. The repeated pair raises IntegrityError and the count remains one, while a different owner could use the same request key. That is the desired rule only if the key is owner-scoped.

Use NOT NULL too when NULL is not a valid identity, because SQLite permits multiple NULLs in a UNIQUE constraint. The constraint rejects a duplicate but does not return an existing job, merge values, or choose retry semantics.

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(owner TEXT, request_key TEXT, UNIQUE(owner, request_key))")
    connection.execute("INSERT INTO job VALUES (?, ?)", ("a", "r1"))
    try:
        connection.execute("INSERT INTO job VALUES (?, ?)", ("a", "r1"))
    except sqlite3.IntegrityError:
        duplicate_rejected = True
    else:
        duplicate_rejected = False
    assert duplicate_rejected
    assert connection.execute("SELECT count(*) FROM job").fetchone()[0] == 1
    print("key=owner+request_key duplicate=rejected")

Expected stdout:

key=owner+request_key duplicate=rejected

Sources

- SQLite UNIQUE constraints

- SQLite conflict resolution

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