Use a UNIQUE constraint to prevent duplicate local keys

By Batu · English technical notes

Also published in our primary archive.

A local key is often used as a stable name for one configuration value. If two rows can share that name, a lookup such as WHERE local_key = ? may no longer identify one setting. This in-memory example makes the rule explicit with local_key TEXT NOT NULL UNIQUE. It stores the key theme once, then attempts to store theme again with a different value.

The second INSERT raises sqlite3.IntegrityError, so the handler labels that specific duplicate attempt as rejected. The query orders by local_key and returns one predictable row, [("theme", "dark")]; the assertion checks both the rejection and the persisted state. Parameter placeholders pass the two input values separately from the SQL text, rather than assembling an SQL statement from the strings.

UNIQUE applies to the values as SQLite compares them under the column’s collation. This default example does not define case-insensitive identity, trimming, Unicode normalization, or a business rule for aliases; choose a collation and normalization strategy deliberately if those matter. SQLite also permits multiple NULL values under a UNIQUE constraint, so NOT NULL is included because this particular key must always exist. The example uses established standard-library APIs and runs on Python 3.6+.

The Python sqlite3 documentation describes execute() and qmark placeholders. SQLite specifies UNIQUE behavior in its CREATE TABLE documentation.

AI assistance disclosure: This article was drafted with AI assistance and should be aligned with the application’s key-normalization rules.

import sqlite3

con = sqlite3.connect(":memory:")
try:
    con.execute("""
        CREATE TABLE setting (
            local_key TEXT NOT NULL UNIQUE,
            value TEXT NOT NULL
        )
    """)
    con.execute(
        "INSERT INTO setting(local_key, value) VALUES (?, ?)",
        ("theme", "dark"),
    )

    try:
        con.execute(
            "INSERT INTO setting(local_key, value) VALUES (?, ?)",
            ("theme", "light"),
        )
    except sqlite3.IntegrityError:
        duplicate_rejected = True
    else:
        duplicate_rejected = False

    rows = con.execute(
        "SELECT local_key, value FROM setting ORDER BY local_key"
    ).fetchall()
    assert duplicate_rejected is True
    assert rows == [("theme", "dark")]

    print("duplicate key: rejected")
    print("stored settings: [('theme', 'dark')]")
finally:
    con.close()

duplicate key: rejected
stored settings: [('theme', 'dark')]