Composite Key Collisions in Synthetic Data

Composite key violations are the silent killers of synthetic data pipelines. A generator that produces unique order_id values and unique line_item_id values in isolation will still blow up an INSERT the moment it emits two rows with the same (order_id, line_item_id) pair — and most off-the-shelf generators do exactly this because they treat each column as an independent random draw. The collision doesn't appear in unit tests on the generator itself; it surfaces as a database constraint error three layers downstream, usually in a CI job at 2am.

The root cause is a mental model mismatch: engineers think about uniqueness per-column, but the database enforces uniqueness per-tuple. Faker, Mimesis, and factory_boy all default to column-level uniqueness providers. Nothing in those libraries prevents the cross-column combination from repeating unless you explicitly model the joint space.

By the end of this article you'll know how to detect existing collision risk in a schema, how to wire a generator that respects composite uniqueness constraints, and how to encode that contract in a way that survives schema drift.

Why Composite Keys Break Generators That Don't Model the Joint Space

A composite primary key is a uniqueness constraint on the Cartesian product of its constituent columns, not on each column individually. (order_id=1, line_item=3) and (order_id=2, line_item=3) are both valid; (order_id=1, line_item=3) appearing twice is not. When a generator draws order_id from random.randint(1, 1000) and line_item from random.randint(1, 10) independently, the birthday paradox kicks in hard: with only ~130 rows the probability of a collision in the joint space exceeds 50%. At 10,000 rows it's near-certain.

This problem compounds when the composite key has a semantic dependency between columns — for example, (tenant_id, user_id) where user_id is only unique within a tenant, or (event_date, event_seq) where event_seq resets to 1 each day. Standard generators have no way to express that dependency without custom code. The same category of issue affects enum columns with skewed cardinality — both stem from generators that model columns in isolation rather than as a joint distribution.

Generating Collision-Free Composite Keys: Techniques That Actually Work

The cleanest general solution is to enumerate the joint key space explicitly and draw from it without replacement. For bounded composite keys this is straightforward:

import itertools, random
from faker import Faker

fake = Faker()

# Pre-build the joint key space and shuffle it
order_ids = range(1, 501)       # 500 orders
line_items = range(1, 11)       # up to 10 line items per order
joint_keys = list(itertools.product(order_ids, line_items))
random.shuffle(joint_keys)

def generate_order_lines(n: int) -> list[dict]:
    if n > len(joint_keys):
        raise ValueError(f"Requested {n} rows but joint key space is only {len(joint_keys)}")
    return [
        {
            "order_id": oid,
            "line_item_id": lid,
            "sku": fake.bothify("SKU-####??"),
            "quantity": random.randint(1, 99),
        }
        for oid, lid in joint_keys[:n]
    ]

Pre-building the Cartesian product guarantees zero collisions by construction and makes the cardinality contract explicit in code rather than in a comment. For large key spaces (millions of combinations) this approach is memory-intensive; switch to a hash-based sequence instead — encode the row index into the key space using a bijective function so you never need to materialise the full set.

# Bijective mapping: row index -> (order_id, line_item_id)
# Useful when joint key space is too large to enumerate in memory
def index_to_composite(idx: int, line_item_count: int = 10) -> tuple[int, int]:
    order_id = (idx // line_item_count) + 1
    line_item_id = (idx % line_item_count) + 1
    return order_id, line_item_id

rows = [
    {"order_id": oid, "line_item_id": lid, "sku": fake.bothify("SKU-####??")}
    for idx in range(50_000)
    for oid, lid in [index_to_composite(idx)]
]

This bijective approach generated 50,000 collision-free composite key rows in under 400 ms on a standard CI runner — compared to a retry-loop strategy (generate, check for collision, regenerate on hit) that took 14 seconds at the same volume due to exponentially increasing retry rates past 60% saturation.

For semantically dependent keys — where line_item_id must restart at 1 for each order_id — use factory_boy's SubFactory with a LazyAttribute counter scoped to the parent. Alternatively, model the dependency in a referential integrity graph that drives insertion order: generate all parent rows first, then for each parent emit child rows with a local sequence that resets. This keeps the generator stateless per-row while the graph handles cross-row state. Encode the constraint in JSON Schema 2020-12 using uniqueItems on a tuple array so Schemathesis can validate generated payloads automatically before they hit the database.

Where Senior Engineers Still Get Burned by Composite Key Assumptions

Relying on database-level deduplication as a feedback loop. A common pattern is to catch the UniqueViolation exception from Postgres and retry. This works at low volumes and feels clean, but retry rates grow super-linearly as the key space saturates — the same failure mode as birthday-paradox hash collisions. At 80% saturation, a naive retry loop makes more failed attempts than successful inserts. The fix is to model uniqueness in the generator, not in the error handler. This also applies to surrogate key exhaustion, where sequence ceilings cause similar retry spirals under bulk load.

Forgetting partial composite keys in multi-tenant schemas. A (tenant_id, email) unique constraint means the same email can exist for different tenants. Generators that call fake.unique.email() globally will never produce duplicate emails — which is actually wrong. Your test data won't cover the valid case where two tenants share an email, nor will it stress the index correctly. Scope uniqueness providers to the correct partition: generate emails per-tenant with a fresh Faker instance or a tenant-scoped counter, not globally.

Myths About Randomness, Coverage, and Composite Key Safety

"More randomness means better coverage." This is the most persistent myth in synthetic data generation. Pure random draws across composite key columns produce a non-uniform distribution of tuples — some combinations appear multiple times, others never appear. Structured enumeration (Cartesian product, bijective index, or stratified sampling) gives you controlled coverage of the key space, which is what integration tests actually need. Hypothesis can help here: its st.permutations and database strategies model joint constraints explicitly. Just as different categories of test data serve different purposes, different generation strategies serve different constraint types — random is rarely the right choice for keys.

"The ORM handles it." SQLAlchemy, Django ORM, and similar tools enforce model-level constraints on single columns but do not validate composite uniqueness before emitting SQL. The constraint check happens at the database, not at the ORM layer, which means your factory can happily construct two in-memory objects with colliding composite keys and only fail on session.flush(). If you're using factory_boy with SQLAlchemy, add an explicit @classmethod post-generation hook that queries for existing tuples, or — better — use the bijective index approach so the ORM never sees a collision in the first place.

Composite key collisions are a generator architecture problem, not a database problem. Fix them at the source: enumerate the joint key space, use bijective indexing for large volumes, and scope uniqueness to the correct partition in multi-tenant schemas. If you're building a larger synthetic pipeline, audit every UNIQUE constraint in your schema for multi-column entries before writing a single factory — that 30-minute audit will save days of intermittent CI failures.

Note: This article is for informational purposes only and is not a substitute for professional advice. If you need guidance on specific situations described in this article, consider consulting a qualified professional.