Use a composite primary key for a mapping table

By Batu · English technical notes

Also published in our primary archive.

A mapping table represents a relationship, so its identity can be the combination of its two endpoints rather than a separate generated identifier. This schema declares PRIMARY KEY (member_id, group_id). It allows member 7 to belong to group 10 and group 11, because the pairs differ. A second attempt to insert (7, 10) conflicts with the composite key and raises sqlite3.IntegrityError.

The program catches that expected exception, retrieves mappings in a defined ORDER BY member_id, group_id, and asserts the result is exactly [(7, 10), (7, 11)]. Those checks establish the behavior of these sample statements in the temporary database: distinct pairs remain available and the duplicate pair is not inserted. The three output lines present the same conclusion without depending on a SQLite-generated error-message string.

The individual columns are explicitly NOT NULL. That matters in SQLite because composite primary keys on ordinary rowid tables have historical behavior that can permit NULL values in some circumstances. If each mapping must also reference real member and group rows, add foreign-key declarations and enable their enforcement for every connection; the composite key alone does not create those relationships. It also does not determine how concurrent callers should resolve a duplicate attempt. This code uses no newer sqlite3 APIs and runs on Python 3.6+.

Use the Python sqlite3 documentation for the DB-API calls. SQLite documents composite primary keys and constraint behavior in CREATE TABLE.

AI assistance disclosure: This article was drafted with AI assistance and should be adapted when the mapping has additional identity rules.

import sqlite3

con = sqlite3.connect(":memory:")
try:
    con.execute("""
        CREATE TABLE membership (
            member_id INTEGER NOT NULL,
            group_id INTEGER NOT NULL,
            PRIMARY KEY (member_id, group_id)
        )
    """)
    con.executemany(
        "INSERT INTO membership(member_id, group_id) VALUES (?, ?)",
        [(7, 10), (7, 11)],
    )

    try:
        con.execute(
            "INSERT INTO membership(member_id, group_id) VALUES (?, ?)",
            (7, 10),
        )
    except sqlite3.IntegrityError:
        duplicate_pair_rejected = True
    else:
        duplicate_pair_rejected = False

    mappings = con.execute(
        "SELECT member_id, group_id FROM membership "
        "ORDER BY member_id, group_id"
    ).fetchall()
    assert duplicate_pair_rejected is True
    assert mappings == [(7, 10), (7, 11)]

    print("duplicate mapping: rejected")
    print("stored mappings: [(7, 10), (7, 11)]")
finally:
    con.close()

duplicate mapping: rejected
stored mappings: [(7, 10), (7, 11)]