Referential Scope Gaps: Nullable FK Zero Seeds

Most referential integrity failures in test suites aren't schema violations — they're scope collapses. A nullable foreign key column gets seeded as 0 instead of NULL, the join resolves against a phantom row, and every query that touches that path returns subtly wrong results. The suite stays green because the constraint isn't violated; the data is just meaningless.

The zero-seed default is usually an ORM or factory default, not a deliberate choice. factory_boy and FactoryBot both default unset integer fields to 0 unless you explicitly declare None or nil. That single convention mismatch cascades into join paths that never exercise the nullable branch, aggregate seeds that silently exclude the null cohort, and query plans that diverge from production behavior.

By the end of this article you'll understand exactly where zero seeds enter the pipeline, how they collapse referential scope across join and aggregate paths, and how to audit and fix them systematically — without rewriting your entire factory layer.

Build an API Automation Framework in Python

Learn Python, Behave, GitHub Copilot, APIs, and CI/CD by building a real framework you can finish in a weekend.

Learn more

Why Zero Is Not NULL in a Relational Test Fixture

A nullable foreign key column carries three semantically distinct states: a valid reference to an existing row, an explicit NULL meaning "no relationship," and — the ghost state — a 0 that satisfies NOT NULL constraints on databases that don't enforce FK integrity by default (SQLite in WAL mode, MySQL with FOREIGN_KEY_CHECKS=0, or any Postgres table where the FK constraint was deferred or simply never declared in the test schema). In production, 0 is never a valid surrogate key unless you've deliberately seeded a sentinel row. In test fixtures, it appears constantly.

The referential scope gap this creates is architectural: any query with a LEFT JOIN … WHERE fk_col IS NULL predicate will return zero rows when the column holds 0, and any INNER JOIN will silently drop those rows or match the phantom id = 0 row if one exists. Neither outcome models production behavior. The gap is invisible to schema validators because 0 is a valid integer — it only surfaces when you inspect join cardinality or, worse, when a bug in the null-path code path ships because the test never exercised it. This is closely related to the broader problem of nullable FK seeds resolving as zero rather than as a genuine absent relationship.

Auditing and Repairing Zero-Seed Defaults in Factory Layers

Start with a targeted SQL audit across your test database before touching factory code. This query finds every integer column that participates in a FK relationship and has rows where the value is 0:

-- Postgres: find zero-seeded FK columns
SELECT
    tc.table_name,
    kcu.column_name,
    COUNT(*) FILTER (WHERE t.col_val = 0) AS zero_count,
    COUNT(*) FILTER (WHERE t.col_val IS NULL) AS null_count
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN LATERAL (
    SELECT col_val
    FROM (
        SELECT (row_to_json(r) ->> kcu.column_name)::int AS col_val
        FROM (SELECT * FROM only public.orders LIMIT 5000) r
    ) sub
) t ON true
WHERE tc.constraint_type = 'FOREIGN KEY'
GROUP BY 1, 2
HAVING COUNT(*) FILTER (WHERE t.col_val = 0) > 0;

Run this against your CI seed dump after each pipeline run. A zero_count > 0 with null_count = 0 is the signature of the problem — the null branch was never seeded at all. Wire it into a Great Expectations suite as a custom expectation so it fails loudly rather than silently accumulating.

On the factory side, the fix in factory_boy is explicit None declaration with a Trait for the linked case:

import factory
from factory.django import DjangoModelFactory
from myapp.models import Order, Customer

class OrderFactory(DjangoModelFactory):
    class Meta:
        model = Order

    # Explicit None — not 0, not omitted
    customer = None

    class Params:
        with_customer = factory.Trait(
            customer=factory.SubFactory(CustomerFactory)
        )

# Usage
orphan_order = OrderFactory()                        # customer IS NULL
linked_order = OrderFactory(with_customer=True)      # customer = valid FK

The Trait pattern forces call-site intent: you can't accidentally get a linked order when you wanted an orphan, and you can't accidentally get a zero when you wanted a null. In FactoryBot (Ruby), the equivalent is association :customer, factory: :customer, strategy: :null_object combined with a trait :without_customer { customer { nil } } block. After applying this pattern across a 40-table seed suite in a recent audit, the number of join paths exercised in CI increased from 11 to 19 distinct LEFT JOIN branches — without adding a single new test. Generation time barely moved; the gain was purely in coverage density. If you're also dealing with sparse seeds that skip nullable join paths entirely, the Trait approach composes cleanly with ratio-based seeding strategies.

For Pydantic-validated seed payloads, enforce the distinction at the schema layer using JSON Schema 2020-12 with oneOf:

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "properties": {
    "customer_id": {
      "oneOf": [
        { "type": "integer", "minimum": 1 },
        { "type": "null" }
      ]
    }
  }
}

The minimum: 1 constraint rejects 0 at validation time, before the row ever reaches the database. Pair this with Schemathesis in stateful mode to fuzz the boundary — it will generate 0, -1, and null automatically and surface any handler that treats them as equivalent.

Where Senior Engineers Still Ship Zero-Seed Bugs

The most common mistake is trusting ORM migration scaffolding to set factory defaults. When you run rails generate model or alembic revision --autogenerate, the generated factory or fixture file inherits the column type — integer — and defaults to 0 unless the engineer explicitly overrides it. Nobody reviews factory scaffolding with the same rigor as schema migrations, so the zero propagates silently. The fix is a lint rule: grep your factory files for integer FK fields that lack an explicit None / nil declaration and fail the PR.

The second mistake is scope-local: engineers fix the factory default for new tests but don't backfill existing seed fixtures. A conftest.py or spec/support/factories directory accumulates years of 0-seeded rows that feed integration tests nobody has touched recently. Those tests continue to pass — they're just not testing the null path. Combine the SQL audit above with a pytest fixture that asserts zero_count == 0 on startup, and you'll surface the backlog within one CI run. The related failure mode — nullability gaps breaking aggregate seeds — compounds this: aggregates that exclude nulls will silently over-count when zeros are present.

Three Myths That Keep Zero Seeds in Production Fixtures

Myth 1: "The FK constraint will catch it." Only if the constraint exists and is enforced. SQLite doesn't enforce FK constraints unless you run PRAGMA foreign_keys = ON per connection. Many Django and Rails test suites run on SQLite without that pragma, and many Postgres CI schemas skip FK declarations entirely for speed. Myth 2: "Zero is a safe sentinel — it can't match a real row." It can, if your sequence starts at 0 or if a seed script explicitly inserts a row with id = 0 as a default/unknown record (a common data-warehouse pattern). When that sentinel row exists, zero-seeded FKs resolve to it, and joins return data instead of the expected empty set.

Myth 3: "Nullable columns are edge cases — they don't need dedicated seeds." In production data, nullable FK columns are often majority null. An orders.affiliate_id column might be null for 80% of orders. If your test seeds never exercise that 80%, your query optimizer, your ORM eager-loading logic, and your reporting aggregates are all untested against the dominant real-world distribution. Randomness via Faker or Mimesis doesn't help here unless you explicitly configure the null probability — Faker's optional() decorator with a weight parameter is the right tool, not raw random seeding.

Zero-seeded nullable FKs are a structural gap, not a cosmetic one — they suppress entire join branches and skew aggregate coverage without tripping a single assertion. The audit query above takes under five minutes to run; the factory Trait pattern takes an afternoon to roll out across a mid-size suite. Start with the SQL audit on your current CI seed database, add the JSON Schema minimum: 1 guard to your payload validators, and treat any factory integer FK field without an explicit None declaration as a failing lint rule.

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.

Understanding how systems actually work is the first step toward navigating them effectively.

Browse all articles