Python SQLite UNIQUE with NULL: an absent key can repeat

Operation contract

An in-memory receipt table declares a unique external ID, then inserts two rows without one. Both rows survive. The fixture separately attempts two equal non-null IDs and observes an IntegrityError. This distinction matters when a deduplication key must be present for every accepted row.

Failure and ownership boundary

Use NOT NULL together with UNIQUE when the business rule requires exactly one non-null key. A nullable unique column can be valid when absence is allowed, but it is not a replay identity. Python SQLite foreign keys: enable enforcement on each connection, Python SQLite job project: reject conflicting replays by request identity and Python sqlite3 transactions: bind values and roll back failed batches supply adjacent guarantees.

Working program

import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("CREATE TABLE receipt(id INTEGER PRIMARY KEY, external_id TEXT UNIQUE)")
connection.execute("INSERT INTO receipt(external_id) VALUES (NULL), (NULL), ('R41')")
try:
    connection.execute("INSERT INTO receipt(external_id) VALUES ('R41')")
except sqlite3.IntegrityError:
    print("duplicate ID rejected")
print("missing IDs:", connection.execute("SELECT COUNT(*) FROM receipt WHERE external_id IS NULL").fetchone()[0])
connection.close()

Output

duplicate ID rejected
missing IDs: 2

Costs and limits

The demonstration inserts a fixed number of rows. Indexed uniqueness has storage proportional to retained distinct values; contention and durable commit behavior are not measured here.

Common Mistakes

• UNIQUE alone does not prohibit multiple NULLs.
• A missing deduplication key cannot identify a replayed event.

Connected lessons

Python SQLite foreign keys: enable enforcement on each connection, Python SQLite job project: reject conflicting replays by request identity, Python sqlite3 transactions: bind values and roll back failed batches.