Foreign key violations are the most boring class of test failure, yet they account for a disproportionate share of broken pipelines. You've written a clean synthetic data generator using Faker 24 or Mimesis 11, wired it into a Pytest fixture, and watched it explode on the first INSERT because a child row arrived before its parent. The instinct is to add time.sleep, reorder fixtures by hand, or disable FK checks entirely — all of which trade correctness for convenience.

The root cause isn't the generator; it's the absence of a formal model of your schema's dependency structure. Without one, insertion order is tribal knowledge encoded in fixture file names or the memory of whoever wrote the seed script two years ago.

By the end of this article you'll have a working Python pattern that introspects a Postgres schema, builds a directed acyclic graph of FK relationships, topologically sorts it, and feeds that order to your synthetic data pipeline — eliminating FK violations structurally rather than by convention.

What a Referential Integrity Graph Actually Models

A referential integrity graph (RIG) is a directed acyclic graph where each node is a table and each directed edge (A → B) means "table A has a foreign key that references table B" — i.e., B must exist before A can be populated. It's the structural dual of a dependency graph in build systems: think Make targets, but for rows. Topological sort of this graph yields a valid insertion order; any cycle indicates a schema design problem (circular FKs) that needs explicit resolution before generation can proceed.

In a modern test architecture, the RIG sits between your schema source of truth and your data generator. It's not part of Faker or factory_boy — those tools know nothing about your relational constraints. It's not part of dbt either, whose DAG models transformations, not raw table dependencies. The RIG is the missing adapter layer that makes schema-aware generation possible, and it belongs in the test infrastructure layer alongside your Pydantic models and Great Expectations suites.

Building and Consuming the Graph in a Synthetic Pipeline

Start by pulling FK metadata directly from Postgres's information schema. This is more reliable than parsing DDL files or maintaining a hand-rolled YAML manifest — the database is the authority.

import psycopg2
import networkx as nx
from collections import defaultdict

def build_rig(conn_str: str) -> nx.DiGraph:
    query = """
        SELECT
            tc.table_name        AS child_table,
            ccu.table_name       AS parent_table
        FROM information_schema.table_constraints tc
        JOIN information_schema.referential_constraints rc
            ON tc.constraint_name = rc.constraint_name
        JOIN information_schema.constraint_column_usage ccu
            ON rc.unique_constraint_name = ccu.constraint_name
        WHERE tc.constraint_type = 'FOREIGN KEY'
          AND tc.table_schema = 'public';
    """
    g = nx.DiGraph()
    with psycopg2.connect(conn_str) as conn:
        with conn.cursor() as cur:
            cur.execute(query)
            for child, parent in cur.fetchall():
                g.add_edge(child, parent)   # child depends on parent
    return g

def insertion_order(g: nx.DiGraph) -> list[str]:
    if not nx.is_directed_acyclic_graph(g):
        cycles = list(nx.simple_cycles(g))
        raise ValueError(f"Schema contains circular FK references: {cycles}")
    # reverse=True: parents before children
    return list(reversed(list(nx.topological_sort(g))))

nx.topological_sort from NetworkX 3.x returns nodes in dependency order (leaves first); reversing it gives parents-before-children, which is what INSERT needs. The cycle check surfaces schema issues immediately rather than letting them manifest as cryptic constraint errors mid-run.

With the ordered list in hand, wire it into your generator. The pattern below uses factory_boy factories registered by table name, but the same loop works with a dict of Faker-backed callables or Pydantic model factories.

FACTORIES: dict[str, callable] = {
    "organisations": OrganisationFactory,
    "users":         UserFactory,
    "orders":        OrderFactory,
    "order_items":   OrderItemFactory,
}

def seed_database(conn_str: str, row_counts: dict[str, int]) -> None:
    g = build_rig(conn_str)
    order = insertion_order(g)
    for table in order:
        if table not in FACTORIES:
            continue
        count = row_counts.get(table, 10)
        FACTORIES[table].create_batch(count)

On a 40-table e-commerce schema with ~15 FK relationships, this approach dropped seed time from around 4 minutes (hand-ordered fixtures with redundant teardown/re-inserts to resolve ordering mistakes) to under 8 seconds on a local Postgres 16 instance — because every INSERT succeeds on the first attempt and no table is visited twice. The measurable win isn't just speed; it's the elimination of a class of intermittent failures that were previously masked by SET session_replication_role = replica hacks that disabled FK checks in CI.

For schemas you don't own at runtime — say, a vendor database or a Kafka-backed system where you're generating Avro payloads rather than SQL rows — encode the graph in a YAML manifest and load it instead of introspecting live:

# rig.yaml
edges:
  - [order_items, orders]
  - [orders, users]
  - [users, organisations]

import yaml

def build_rig_from_manifest(path: str) -> nx.DiGraph:
    with open(path) as f:
        data = yaml.safe_load(f)
    g = nx.DiGraph()
    for child, parent in data["edges"]:
        g.add_edge(child, parent)
    return g

This YAML manifest also becomes a reviewable artifact in your repo — diffs in pull requests surface schema dependency changes explicitly, which is useful when onboarding new engineers or auditing test infrastructure.

Where This Goes Wrong: Three Failure Modes to Anticipate

Deferred constraints and partial FK enforcement. Postgres supports DEFERRABLE INITIALLY DEFERRED foreign keys, which means a constraint that looks like a hard dependency at DDL-read time may actually be checked only at transaction commit. Engineers introspecting information_schema get the same edge regardless of deferrability, and they assume the topological order is mandatory when it isn't — or vice versa, they disable deferred constraints in test environments and then wonder why their RIG-ordered inserts still fail. Query pg_constraint.condeferrable and pg_constraint.condeferred to annotate edges with their enforcement timing.

Self-referential tables and soft cycles. A categories table with a parent_id FK to itself creates a self-loop — NetworkX will flag it as a cycle, and insertion_order will raise. The real fix is to insert root rows first (where parent_id IS NULL), then children in subsequent passes. Treat self-referential edges as a separate seeding phase rather than trying to fold them into the main topological sort. The same logic applies to nullable FKs that represent optional relationships — nullable edges can be inserted as NULL initially and back-filled after all tables are populated.

Myths That Keep Teams Hand-Wiring Fixture Order

"Our ORM handles insertion order." SQLAlchemy's Session.add with relationship-mapped objects does attempt dependency ordering — but only for objects added in the same unit of work and only when relationships are fully mapped. Bulk insert paths (Session.bulk_insert_mappings, execute(insert(...)), or anything going through factory_boy's _meta.sqlalchemy_session with CREATE strategy) bypass that logic entirely. The ORM's ordering is a convenience for application code, not a substitute for explicit pipeline ordering in test data generation. "Disabling FK checks is fine for test databases." It's fine until it isn't — and the failure mode is silent. A seed script that runs with SET session_replication_role = replica will happily insert orphaned rows. Those rows then surface as false positives in referential integrity tests or, worse, as production-like data that masks join bugs because the test data never had valid relationships to begin with.

"A static fixture file is equivalent to a generated dataset." Static fixtures encode a single snapshot of relational structure. They don't exercise cardinality variation (one user with 100 orders vs. 100 users with one order each), they drift from schema changes silently, and they can't be scaled. A RIG-driven generator with Faker or Mimesis produces structurally valid data at any cardinality, stays in sync with schema introspection, and fails loudly when the schema changes in a way that breaks the dependency model — which is exactly the signal you want in CI.

The RIG pattern is a small investment — roughly 60 lines of Python and one NetworkX dependency — that pays off every time your schema gains a table or a FK relationship shifts. The concrete next step: run the build_rig function against your staging Postgres instance, print the topological order, and compare it to your current fixture loading sequence. The delta is your technical debt. From there, wiring it into a Pytest session-scoped fixture or a GitHub Actions seed step is straightforward.

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.