Test Data Generation for Unique, Check, and

Test Data Generation for Unique, Check, and Not‑Null Constraints

When a test suite hits a database, the most common cause of flaky or failing runs isn’t the application logic—it’s the data.
Constraints such as UNIQUE, CHECK, and NOT NULL are the first line of defense for data integrity, yet they are also the most frequent source of “cannot insert duplicate key”, “check constraint violated”, or “null value in column violates not‑null constraint” errors during automated test execution.

This guide walks through a repeatable workflow for generating test data that respects those constraints, shows a worked example on a realistic schema, highlights tool considerations, and ends with a concrete next step you can take today.

1. Why Constraints Break Test Data Pipelines

Constraint	Typical Failure Mode	Root Cause in Test Data Generation
UNIQUE	Duplicate key error on insert	Random generators produce the same value twice; static seed data reused across parallel runs
CHECK	Value outside allowed range or pattern	Generators ignore business rules (e.g., `age BETWEEN 0 AND 120`, `email LIKE '%@%.%'`)
NOT NULL	Null inserted into mandatory column	Optional fields left empty; nullable‑by‑default libraries omit columns

These failures surface early in CI pipelines, but they also hide deeper problems:

Non‑deterministic tests – a run passes locally but fails on the build server.
Masking real bugs – the test aborts before exercising the code path you actually wanted to verify.
Maintenance overhead – developers spend time “fixing data” instead of fixing code.

A disciplined generation strategy eliminates the noise and lets the test suite focus on behavior.

2. Decision Criteria for a Generation Approach

Before picking a tool or writing a script, answer the following questions. The answers shape the architecture of your data‑generation pipeline.

Question	Decision Impact
Schema volatility – How often do tables, columns, or constraints change?	High volatility → schema‑aware generator (code‑first or migration‑driven). Low volatility → static seed files may suffice.
Parallel test execution – Do you run multiple test workers against the same DB instance?	Yes → need per‑run uniqueness (e.g., UUID, sequence, or tenant prefix). No → simple global counters work.
Data realism – Do tests need production‑like distributions (e.g., zip‑code frequency, name diversity)?	Yes → use domain‑specific libraries or synthetic data services. No → simple random values are enough.
Regulatory / privacy constraints – Can you use real PII?	No → must anonymize or synthesize; generator must support masking rules.
Team skill set – SQL‑heavy vs. code‑heavy (Python, Java, JS)?	Choose a generator that matches the language your CI jobs already use.
Feedback loop – How fast must a new data set be ready?	Sub‑second → in‑process library. Minutes → external CLI or SaaS is acceptable.

Rule of thumb: start with the simplest approach that satisfies the most restrictive answer. You can always add sophistication later.

3. Core Generation Patterns

3.1. Deterministic Unique Values

Pattern	Implementation Sketch	When to Use
UUID / ULID	`uuid_generate_v4()` (Postgres) or `uuid()` (MySQL)	Global uniqueness, no coordination needed.
Sequence + Prefix	`nextval('seq_user_id')
Hash of Test Context	`md5(concat(test_class, method, timestamp))::uuid`	Guarantees repeatability for a given test case.

Tip: Never rely on RANDOM() alone for a UNIQUE column; collisions are inevitable under parallelism.

3.2. CHECK‑Constraint‑Aware Value Generation

Parse the constraint definition (most DBs expose information_schema.check_constraints or pg_constraint).
Extract the predicate – e.g., age BETWEEN 0 AND 120.
Map to a generator – range integer, regex for strings, enum list for IN (…) clauses.
Validate – after generation, run a lightweight SELECT 1 FROM table WHERE NOT (check_predicate) LIMIT 1 to catch any drift.

3.3. NOT NULL Enforcement

Schema‑first: generate a column list from information_schema.columns WHERE is_nullable = 'NO' and feed it to the generator as required fields.
Default‑value fallback: if a column has a DEFAULT expression, you may omit it; otherwise you must supply a value.

4. Worked Example: E‑Commerce Order Schema

Below is a compact but realistic schema that exercises all three constraint types.

CREATE TABLE customers (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email         TEXT NOT NULL UNIQUE,
    full_name     TEXT NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);


CREATE TABLE products (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sku           TEXT NOT NULL UNIQUE,
    name          TEXT NOT NULL,
    price_cents   INT NOT NULL CHECK (price_cents > 0),
    stock_qty     INT NOT NULL CHECK (stock_qty >= 0)
);


CREATE TABLE orders (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id   UUID NOT NULL REFERENCES customers(id),
    status        TEXT NOT NULL CHECK (status IN ('pending','paid','shipped','cancelled')),
    total_cents   INT NOT NULL CHECK (total_cents >= 0),
    placed_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);


CREATE TABLE order_items (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id      UUID NOT NULL REFERENCES orders(id),
    product_id    UUID NOT NULL REFERENCES products(id),
    quantity      INT NOT NULL CHECK (quantity > 0),
    line_total_cents INT NOT NULL CHECK (line_total_cents >= 0),
    UNIQUE (order_id, product_id)               -- one line per product per order
);

4.1. Generation Plan

Table	Unique Columns	Check Columns	Not‑Null Columns (no default)
customers	email	–	full_name
products	sku	price_cents > 0, stock_qty >= 0	name
orders	–	status IN (…), total_cents >= 0	customer_id
order_items	(order_id, product_id)	quantity > 0, line_total_cents >= 0	order_id, product_id

4.2. Python‑Style Pseudocode (using `Faker` + custom validators)

import uuid, random
from faker import Faker
from datetime import datetime, timezone


fake = Faker()


# ---------- Helper generators ----------


def unique_email(run_id: str) -> str:
    # deterministic per run, avoids collisions across parallel workers
    return f"test+{run_id}+{uuid.uuid4().hex[:8]}@example.com"


def sku_generator(run_id: str) -> str:
    return f"SKU-{run_id}-{uuid.uuid4().hex[:6].upper()}"


def status_generator() -> str:
    return random.choice(['pending','paid','shipped','cancelled'])


# ---------- Core generation ----------


def build_dataset(run_id: str, num_customers=10, num_products=20, num_orders=30):
    customers = []
    for _ in range(num_customers):
        customers.append({
            "id": uuid.uuid4(),
            "email": unique_email(run_id),
            "full_name": fake.name(),
            "created_at": datetime.now(timezone.utc)
        })


products = []
    for _ in range(num_products):
        products.append({
            "id": uuid.uuid4(),
            "sku": sku_generator(run_id),
            "name": fake.word().capitalize() + " " + fake.word().capitalize(),
            "price_cents": random.randint(100, 500_00),   # $1 – $5,000
            "stock_qty": random.randint(0, 500)
        })


# Map for FK resolution
    cust_by_id = {c["id"]: c for c in customers}
    prod_by_id = {p["id"]: p for p in products}


orders = []
    order_items = []
    for _ in range(num_orders):
        cust = random.choice(customers)
        order_id = uuid.uuid4()
        status = status_generator()
        # compute total from items later
        orders.append({
            "id": order_id,
            "customer_id": cust["id"],
            "status": status,
            "total_cents": 0,          # placeholder
            "placed_at": datetime.now(timezone.utc)
        })


# 1‑3 items per order
        for _ in range(random.randint(1, 3)):
            prod = random.choice(products)
            qty = random.randint(1, 5)
            line_total = prod["price_cents"] * qty
            order_items.append({
                "id": uuid.uuid4(),
                "order_id": order_id,
                "product_id": prod["id"],
                "quantity": qty,
                "line_total_cents": line_total
            })


# Back‑fill order totals
    from collections import defaultdict
    totals = defaultdict(int)
    for item in order_items:
        totals[item["order_id"]] += item["line_total_cents"]
    for o in orders:
        o["total_cents"] = totals[o["id"]]


return {
        "customers": customers,
        "products": products,
        "orders": orders,
        "order_items": order_items
    }

Key points in the script

Run‑scoped identifiers (run_id) guarantee uniqueness across parallel CI jobs.
Check constraints are satisfied by construction (price_cents > 0, quantity > 0, status enum).
NOT NULL columns are always populated; columns with defaults (created_at, placed_at) are still supplied for determinism.
Foreign‑key integrity is enforced by selecting from already‑generated parent lists.

4.3. Loading the Data

def load(dataset, conn):
    with conn.cursor() as cur:
        # Customers
        cur.executemany(
            "INSERT INTO customers (id,email,full_name,created_at) VALUES (%(id)s,%(email)s,%(full_name)s,%(created_at)s)",
            dataset["customers"]
        )
        # Products
        cur.executemany(
            "INSERT INTO products (id,sku,name,price_cents,stock_qty) VALUES (%(id)s,%(sku)s,%(name)s,%(price_cents)s,%(stock_qty)s)",
            dataset["products"]
        )
        # Orders
        cur.executemany(
            "INSERT INTO orders (id,customer_id,status,total_cents,placed_at) VALUES (%(id)s,%(customer_id)s,%(status)s,%(total_cents)s,%(placed_at)s)",
            dataset["orders"]
        )
        # Order items
        cur.executemany(
            "INSERT INTO order_items (id,order_id,product_id,quantity,line_total_cents) VALUES (%(id)s,%(order_id)s,%(product_id)s,%(quantity)s,%(line_total_cents)s)",
            dataset["order_items"]
        )
    conn.commit()

Running the generator with a distinct run_id per CI job (e.g., github.sha + job_index) yields a collision‑free, constraint‑valid data set every time.

5. Tool Landscape

Category	Representative Tools	Strengths	Weaknesses
Schema‑aware libraries	`sqlalchemy-utils`, `django-test-data`, `go-testfixtures`	Direct ORM integration, auto‑detect NOT NULL/UNIQUE	Limited CHECK parsing; may need custom extensions
Standalone CLI generators	`dbt seed`, `sqitch`, `pg_faker`, `mysqlslap`	DB‑agnostic, can run in any pipeline step	Often require manual constraint mapping
Synthetic data platforms	QA3 free test data generator (`/tools/test-data-generator`), Mockaroo, Tonic, Gretel	UI for non‑coders, built‑in constraint handling, export to SQL/CSV	SaaS latency, possible data‑privacy review
In‑process fakers	`Faker` (Python/JS/Java), `go-faker`, `kotlin-faker`	Fast, programmable, easy to embed in unit tests	No native DB constraint awareness – you add it yourself

Choosing a tool

If you already have an ORM – extend its fixture/factory layer (e.g., Factory Boy, FactoryBot).
If you need a quick, shareable data set for manual QA – use the QA3 free test data generator; it reads a DDL file, infers UNIQUE/CHECK/NOT NULL, and emits a ready‑to‑run SQL script.
If you run massive integration suites – embed a lightweight in‑process generator (like the Python example) to avoid network hops.

6. Validation Checks You Should Automate

Even a well‑designed generator can drift when schema migrations add a new CHECK or a column loses its default. Add these guardrails to your CI pipeline:

Check	Implementation	Frequency
Schema‑generation contract test	Run generator → `INSERT` into a temporary schema → `SELECT 1 FROM information_schema.table_constraints WHERE constraint_type IN ('UNIQUE','CHECK','NOT NULL') AND NOT VALIDATED`	Every PR
Duplicate‑key detector	`SELECT column, COUNT() FROM temp_table GROUP BY column HAVING COUNT() > 1` on all UNIQUE columns	Every PR
Check‑constraint audit	`SELECT conname, pg_get_constraintdef(oid) FROM pg_constraint WHERE contype = 'c'` → feed to a small rule engine that verifies generated rows satisfy each predicate	Nightly
Null‑column scan	`SELECT column_name FROM information_schema.columns WHERE is_nullable='NO' AND column_default IS NULL` → assert every generated row provides a value	Every PR
Foreign‑key closure	After load, run `SET CONSTRAINTS ALL IMMEDIATE;` (Postgres) or `CHECK TABLE` (MySQL)	Every PR

Automating these checks turns “data‑generation bugs” into failed builds, which are far cheaper than flaky test investigations.

7. Common Pitfalls & Mitigations

Pitfall	Symptom	Mitigation
Reusing static seed files across parallel jobs	Intermittent “duplicate key” errors	Parameterize seed with run‑specific prefix/UUID; never commit a static `INSERT` script for UNIQUE columns.
Ignoring CHECK constraints that involve multiple columns (e.g., `CHECK (start_date < end_date)`)	Row passes single‑column generators but fails multi‑column rule	Generate correlated columns together; use a small “domain object” builder that enforces cross‑field invariants.
Assuming DEFAULT covers NOT NULL	Null‑violation on columns that lost their default in a migration	Treat NOT NULL as required unless you explicitly verify a non‑NULL default exists in the current schema.
Generating data that violates business‑level invariants not expressed as DB constraints (e.g., “order total must equal sum of line items”)	Logical bugs surface only in end‑to‑end tests	Encode the invariant in the generator (as shown in the worked example) or add a post‑load validation step.
Over‑generating data volume	Test suite slows down, DB storage spikes	Parameterize row counts per environment (CI: 10‑50 rows; staging: 1 k‑10 k).
Hard‑coding enum values	New status added → generator breaks	Pull enum values from `information_schema.check_constraints` or from the application’s constant definitions at runtime.

8. Scaling the Approach

Scale	Recommended Architecture
Single service, < 20 tables	In‑process generator (Python/JS/Go) + contract test in CI.
Micro‑service fleet, shared DB	Central “test‑data service” exposing a gRPC/HTTP API; each service calls it with its own schema slice.
Large legacy DB, many CHECKs	Use the QA3 free test data generator to bootstrap a baseline SQL script, then extend with custom code for cross‑table invariants.

Test Data Generation for Unique, Check, and Not-Null Constraints