How to Generate Relational Test Data with Valid Foreign Keys

How to Generate Relational Test Data with Valid Foreign Keys

Relational databases are the backbone of most production systems, yet test data generation often treats tables as independent silos. The result? Broken foreign‑key constraints, flaky integration tests, and hours spent debugging “orphan” rows that never exist in production. This guide walks through a repeatable, evidence‑based workflow for producing relational test data that respects every foreign‑key relationship—without hand‑crafting each insert statement.

---

1. Why the Problem Exists

Symptom	Root Cause
INSERT fails with foreign key violation	Child rows reference primary‑key values that were never created.
Tests pass locally but explode in CI	Local DB seeded with a hand‑crafted snapshot; CI uses a fresh schema.
Data‑driven tests become brittle	Hard‑coded IDs assume a specific insertion order.
Performance degrades when “fixing” data on the fly	Runtime INSERT … SELECT cascades lock tables and blow up transaction logs.

The common denominator: generation order and value coordination are not automated. A reliable process must (a) know the dependency graph, (b) produce primary‑key values before they are referenced, and (c) verify the result before the test suite runs.

---

2. Prerequisites

Item	Minimum Viable Version	Why It Matters
Database engine	PostgreSQL 13+, MySQL 8+, SQL Server 2019+, Oracle 19c	All support DEFERRABLE constraints and RETURNING clauses used in the examples.
Schema migration tool	Flyway, Liquibase, or native ALTER TABLE scripts	Guarantees the target schema matches the generation script.
Test‑data generator	Custom SQL, ORM‑based factories, or a dedicated tool (e.g., QA3’s free test data generator at /tools/test-data-generator)	Provides repeatable, parameterised data creation.
CI/CD pipeline	GitHub Actions, GitLab CI, Azure Pipelines, Jenkins	Runs generation + validation on every PR.
Validation harness	psql, mysql, sqlcmd, or a small script in Python/Go/Node	Executes constraint checks automatically.

Tip: Keep the generation code in the same repository as the schema migrations. Version‑control coupling prevents drift.

---

3. Mapping the Dependency Graph

Foreign keys form a directed acyclic graph (DAG) (cycles are only possible with self‑referencing tables, handled later). The first step is to extract that graph automatically.

3.1 Extracting FK Metadata (PostgreSQL Example)

SELECT
    conrelid::regclass          AS child_table,
    confrelid::regclass         AS parent_table,
    conname                     AS fk_name,
    array_agg(attname ORDER BY attnum) AS child_columns,
    array_agg(confattname ORDER BY attnum) AS parent_columns
FROM pg_constraint c
JOIN pg_attribute a  ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
JOIN pg_attribute af ON af.attrelid = c.confrelid AND af.attnum = ANY(c.confkey)
WHERE c.contype = 'f'
GROUP BY conrelid, confrelid, conname;

The query returns rows like:

child_table	parent_table	fk_name	child_columns	parent_columns
order_item	product	fk_order_item_prod	{product_id}	{id}
order_item	orders	fk_order_item_ord	{order_id}	{id}

3.2 Building the Topological Order

from collections import defaultdict, deque


def topo_sort(edges):
    """edges = [(child, parent), ...]  -> list of tables in insert order"""
    indeg = defaultdict(int)
    adj   = defaultdict(list)
    nodes = set()
    for child, parent in edges:
        adj[parent].append(child)
        indeg[child] += 1
        nodes.add(child); nodes.add(parent)


q = deque([n for n in nodes if indeg[n] == 0])
    order = []
    while q:
        n = q.popleft()
        order.append(n)
        for m in adj[n]:
            indeg[m] -= 1
            if indeg[m] == 0:
                q.append(m)
    if len(order) != len(nodes):
        raise ValueError("Cycle detected – handle self‑refs separately")
    return order

Run the query, feed the (child, parent) pairs into topo_sort, and you have a deterministic insertion sequence that guarantees every parent row exists before its children.

---

4. Generation Strategies

Strategy	When It Shines	Trade‑offs
Pure SQL scripts (CTE + RETURNING)	Small‑to‑medium schemas, CI environments without app runtime	Requires manual maintenance when schema changes.
ORM factories (FactoryBot, Django‑factory‑boy, Spring Test DB)	Teams already using an ORM; need object graphs for unit tests	Couples test data to ORM models; slower for bulk loads.
Dedicated data‑generation tools (QA3 generator, DataFactory, Synthesized)	Large schemas, need realistic distributions, PII masking	Extra dependency; learning curve for templating language.
Hybrid (SQL for core tables, ORM for domain‑specific aggregates)	Mixed codebases	More moving parts; must keep both in sync.

4.1 Pure‑SQL Pattern (PostgreSQL)

-- 1. Parent table: product
WITH inserted_product AS (
    INSERT INTO product (sku, name, price_cents)
    SELECT
        'SKU-' || g.i,
        'Product ' || g.i,
        (random() * 5000 + 500)::int
    FROM generate_series(1, 100) AS g(i)
    RETURNING id
)
-- 2. Child table: order_item references product.id
INSERT INTO order_item (order_id, product_id, qty, unit_price_cents)
SELECT
    o.id,
    p.id,
    (random() * 5 + 1)::int,
    p.price_cents
FROM inserted_product p
CROSS JOIN LATERAL (
    SELECT id FROM orders ORDER BY random() LIMIT 1
) o;

Key points

• RETURNING captures generated PKs instantly.
• CROSS JOIN LATERAL picks a random existing parent row without a separate SELECT.
• The whole statement runs in a single transaction → no intermediate constraint violations.

4.2 ORM Factory Example (Python + SQLAlchemy + factory_boy)

import factory
from models import Product, Order, OrderItem, Session


class ProductFactory(factory.alchemy.SQLAlchemyModelFactory):
    class Meta:
        model = Product
        sqlalchemy_session = Session
        sqlalchemy_session_persistence = "commit"


sku = factory.Sequence(lambda n: f"SKU-{n:06d}")
    name = factory.Faker("word")
    price_cents = factory.Faker("pyint", min_value=500, max_value=5000)


class OrderFactory(factory.alchemy.SQLAlchemyModelFactory):
    class Meta:
        model = Order
        sqlalchemy_session = Session
        sqlalchemy_session_persistence = "commit"


customer_id = factory.Faker("pyint", min_value=1, max_value=1000)
    placed_at = factory.Faker("date_time_this_year")


class OrderItemFactory(factory.alchemy.SQLAlchemyModelFactory):
    class Meta:
        model = OrderItem
        sqlalchemy_session = Session
        sqlalchemy_session_persistence = "commit"


order = factory.SubFactory(OrderFactory)
    product = factory.SubFactory(ProductFactory)
    qty = factory.Faker("pyint", min_value=1, max_value=10)
    unit_price_cents = factory.LazyAttribute(lambda o: o.product.price_cents)

Why it works: SubFactory forces creation of the parent (Order, Product) before the child (OrderItem). The session commits each factory, so the DB sees valid FKs at all times.

4.3 Using QA3’s Free Test Data Generator

If you prefer a UI‑driven, schema‑aware approach, the QA3 test data generator (/tools/test-data-generator) can:

1. Introspect your schema (via JDBC/ODBC).
2. Auto‑detect the FK DAG and propose a generation order.
3. Let you define column‑level rules (regex, ranges, foreign‑key lookup, PII masking).
4. Export a single SQL file or CSV set that you can feed into CI.

The generated script follows the same WITH … RETURNING pattern shown above, but you never write the CTEs yourself.

---

5. Worked Example: End‑to‑End Generation for an E‑Commerce Schema

5.1 Schema Overview

erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--o{ ORDER_ITEM : contains
    PRODUCT ||--o{ ORDER_ITEM : referenced
    PRODUCT ||--o{ INVENTORY : tracked
    WAREHOUSE ||--o{ INVENTORY : stores

Tables (simplified):

Table	PK	FKs
customer	id	–
warehouse	id	–
product	id	–
order	id	customer_id → customer.id
order_item	id	order_id → order.id, product_id → product.id
inventory	id	product_id → product.id, warehouse_id → warehouse.id

5.2 Step‑by‑Step Generation Script (PostgreSQL)

-- 0. Clean slate (optional, useful for CI)
TRUNCATE
    order_item,
    "order",
    inventory,
    product,
    customer,
    warehouse
RESTART IDENTITY CASCADE;


-- 1. Independent roots
WITH ins_customer AS (
    INSERT INTO customer (email, full_name, created_at)
    SELECT
        'cust' || g.i || '@example.com',
        'Customer ' || g.i,
        NOW() - (random() * interval '365 days')
    FROM generate_series(1, 500) g(i)
    RETURNING id
),
ins_warehouse AS (
    INSERT INTO warehouse (code, location)
    SELECT
        'WH' || LPAD(g.i::text, 3, '0'),
        'Location ' || g.i
    FROM generate_series(1, 10) g(i)
    RETURNING id
),
ins_product AS (
    INSERT INTO product (sku, name, price_cents, active)
    SELECT
        'SKU-' || LPAD(g.i::text, 6, '0'),
        'Product ' || g.i,
        (random() * 10000 + 1000)::int,
        true
    FROM generate_series(1, 2000) g(i)
    RETURNING id, price_cents
),
-- 2. First‑level children (order depends on customer)
ins_order AS (
    INSERT INTO "order" (customer_id, placed_at, status)
    SELECT
        c.id,
        NOW() - (random() * interval '180 days'),
        (ARRAY['new','paid','shipped','cancelled'])[floor(random()*4)+1]
    FROM ins_customer c
    CROSS JOIN LATERAL (
        SELECT 1 AS dummy
    ) _   -- forces one row per customer; adjust for multiple orders
    RETURNING id
),
-- 3. Second‑level children (order_item depends on order + product)
ins_order_item AS (
    INSERT INTO order_item (order_id, product_id, qty, unit_price_cents)
    SELECT
        o.id,
        p.id,
        (random() * 5 + 1)::int,
        p.price_cents
    FROM ins_order o
    CROSS JOIN LATERAL (
        SELECT id, price_cents FROM ins_product ORDER BY random() LIMIT 3
    ) p
    RETURNING id
),
-- 4. Inventory (depends on product + warehouse)
ins_inventory AS (
    INSERT INTO inventory (product_id, warehouse_id, quantity_on_hand)
    SELECT
        p.id,
        w.id,
        (random() * 200)::int
    FROM ins_product p
    CROSS JOIN ins_warehouse w
    WHERE random() < 0.7   -- 70 % of product‑warehouse combos get stock
    RETURNING id
)
SELECT 1;  -- dummy final result

What the script demonstrates

Phase	Tables Inserted	FK Satisfied By
0	Truncate (optional)	–
1	customer, warehouse, product	No FKs
2	order	customer_id from ins_customer
3	order_item	order_id from ins_order, product_id from ins_product
4	inventory	product_id from ins_product, warehouse_id from ins_warehouse

All INSERT … RETURNING clauses feed the next CTE, guaranteeing zero FK violations without ever disabling constraints.

5.3 Parameterising the Script for CI

Parameter	Example Value	Injection Method
CUSTOMER_COUNT	500	--set CUSTOMER_COUNT=500 (psql) or :CUSTOMER_COUNT (SQLCMD)
ORDER_PER_CUSTOMER	2	Adjusted in CROSS JOIN LATERAL (SELECT generate_series(1, :ORDER_PER_CUSTOMER))
PRODUCT_COUNT	2000	Same as above
SEED	42	SET SEED TO :SEED; before the script (PostgreSQL)

Parameterisation lets the same script produce small fast runs for PR checks and large realistic datasets for performance testing.

---

6. Validation – Prove the Data Is Clean

Generation is only half the battle. Automated validation should run immediately after the generation step, before any test suite touches the DB.

6.1 Constraint‑Only Check (Fast)

-- Run in a separate transaction; will raise if any FK is broken
SET CONSTRAINTS ALL IMMEDIATE;

If the generation script runs inside a single transaction, this is a no‑op. In CI where you may split steps, the command surfaces hidden violations instantly.

6.2 Row‑Count & Referential Integrity Queries

-- 1. Orphan order_items
SELECT COUNT(*) AS orphan_items
FROM order_item oi
LEFT JOIN "order" o ON oi.order_id = o.id
WHERE o.id IS NULL;


-- 2. Products never ordered (optional business rule)
SELECT COUNT(*) AS never_ordered
FROM product p
LEFT JOIN order_item oi ON p.id = oi.product_id
WHERE oi.product_id IS NULL;

Add these as assertive steps in your pipeline (e.g., pytest -k "db_integrity" or a shell script that exits non‑zero on non‑zero counts).

6.3 Data‑Quality Spot Checks

Check	SQL	Threshold
Email format	SELECT COUNT(*) FROM customer WHERE email !~* '^[^@]+@[^@]+\.[^@]+$';	0
Price > 0	SELECT COUNT(*) FROM product WHERE price_cents <= 0;	0
Inventory non‑negative	SELECT COUNT(*) FROM inventory WHERE quantity_on_hand < 0;	0

Automate a small sample (e.g., TABLESAMPLE BERNOULLI (1)) for large tables to keep validation fast.

---

7. Common Failure Modes & Mitigations

Failure Mode	Symptom	Root Cause	Mitigation
**Cycle in FK cycle detection in topo‑sort	Self‑referencing table (employee.manager_id → employee.id)	Topological sort cannot order a cycle	Insert roots first (rows with manager_id IS NULL), then iterate: INSERT … SELECT using RETURNING to feed next level.
**Deferred constraints