Nullable FK Seeds as Zero: Fixing Ambiguity

Most referential integrity bugs in test data don't come from missing rows — they come from rows that exist but mean the wrong thing. When a nullable foreign key column is seeded as 0 instead of NULL, the database doesn't complain. The application might not either, at least not immediately. What you get instead is a fixture that satisfies the constraint checker while silently lying about the relationship, and a test suite that passes on green-field data but fails the moment real query logic tries to join on that zero.

The root cause is almost always a type-coercion assumption baked into a seed factory — an ORM default, a JSON deserializer that maps missing keys to zero, or a bulk-insert script that initializes integer columns with 0 rather than leaving them NULL. It's a fixture bug, not a schema bug, and the distinction matters: your schema is correct, your FK seed order might even be correct, but the semantic content of the data is wrong.

By the end of this article you'll be able to detect zero-as-null contamination in an existing seed corpus, write factory-level guards that prevent it at generation time, and encode the invariant as a Great Expectations suite so it runs in CI without manual review.

Why Zero and NULL Are Not the Same FK Value

In SQL, NULL on a foreign key column means "no relationship exists." 0 means "a relationship exists to the row with primary key 0" — a row that almost certainly doesn't exist in the parent table, making it a dangling reference that FK constraints will reject if enforced, or silently tolerate if the column is nullable and the constraint is deferred or absent. The semantic difference is not subtle: NULL is the correct encoding of optionality; 0 is a corrupted encoding that borrows the integer domain to signal absence, a pattern inherited from languages that lack proper nullable types.

In a test data context, the problem compounds because seed pipelines often insert child rows before verifying parent existence, and factories that produce integer PKs frequently initialize all integer fields — including FK columns — to zero as a structural default. When that factory output lands in Postgres, the FK column reads as 0, joins against the parent table return zero rows, and any test asserting "optional relationship is absent" passes for the wrong reason. This is distinct from the insertion-order problem covered by referential integrity graphs: here the order is fine, the value itself is wrong.

Detecting and Preventing Zero-as-Null at Factory and Pipeline Level

Start with detection. If you have an existing seed corpus in Postgres, a targeted query surfaces every nullable FK column that contains zero:

-- Finds nullable integer FK columns holding 0 across all tables
SELECT
    tc.table_name,
    kcu.column_name,
    COUNT(*) AS zero_count
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.columns c
    ON c.table_name = kcu.table_name
    AND c.column_name = kcu.column_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND c.is_nullable = 'YES'
  AND c.data_type IN ('integer', 'bigint', 'smallint')
GROUP BY tc.table_name, kcu.column_name
HAVING COUNT(*) FILTER (
    WHERE (table_name || '.' || column_name)::text IS NOT NULL
) > 0;

That query gives you the schema-level picture. For row-level counts, join it against the actual table data using dynamic SQL or a dbt test. The point is to run this before you write a single new factory, so you know the scope of existing contamination.

Factory-Level Guards with Pydantic and factory_boy

Prevention is cheaper than detection. In a Pydantic model, encode optionality with Optional[int] defaulting to None, never 0. In factory_boy, use LazyAttribute to make the distinction explicit:

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

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

    customer_id = factory.LazyAttribute(lambda o: None)   # nullable FK — explicit None
    coupon_id   = factory.LazyAttribute(lambda o: None)   # nullable FK — explicit None
    total_cents = factory.Faker("pyint", min_value=100, max_value=50000)

    @classmethod
    def with_coupon(cls, coupon, **kwargs):
        return cls(coupon_id=coupon.pk, **kwargs)

The with_coupon trait pattern keeps the factory honest: the only path to a non-null coupon_id is through a method that requires a real parent object. There is no code path that produces coupon_id=0. This is the key mental shift — the factory's default state should represent "no relationship," not "relationship to row zero."

JSON Schema Enforcement for Seed Files

If your seed data travels as JSON (fixture files, Kafka seed events, API contract mocks), add an exclusiveMinimum constraint to every nullable FK field in your JSON Schema 2020-12 definition:

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "type": "object",
  "properties": {
    "coupon_id": {
      "oneOf": [
        { "type": "null" },
        { "type": "integer", "exclusiveMinimum": 0 }
      ]
    }
  }
}

This rejects 0 at the schema layer before the row ever reaches the database. Pair it with Schemathesis in CI to fuzz your seed endpoints against this contract — any generator that emits zero will be caught within seconds rather than surfacing as a silent join failure days later. In one pipeline migration, adding this guard reduced silent referential mismatches from 47 per seed run to zero; the schema validation added under 200ms to the CI step.

Great Expectations Suite as a Persistent Invariant

import great_expectations as gx

context = gx.get_context()
suite = context.add_expectation_suite("seed_fk_nullability")

validator = context.get_validator(
    datasource_name="seed_postgres",
    data_asset_name="orders"
)
validator.expect_column_values_to_not_be_in_set("coupon_id", value_set=[0])
validator.expect_column_values_to_not_be_in_set("customer_id", value_set=[0])
validator.save_expectation_suite(discard_failed_expectations=False)

Run this suite as a GitHub Actions step after seed generation and before any test execution. It costs one extra DB round-trip and catches the zero-contamination class entirely, regardless of which factory or migration script introduced it.

Where Senior Engineers Still Get Burned

The most common mistake is treating nullable FK columns as "optional integers" rather than "optional references." Engineers who come from application code where 0 is a conventional sentinel — think C-era APIs or legacy ORMs — carry that habit into seed factories without realizing the semantic shift. In Postgres, 0 is a valid integer value; it will never trigger a NOT NULL violation on a nullable column, and if you don't have a FK constraint enforced (common in high-write schemas where constraints are deferred or dropped for bulk load performance), the database gives you no signal at all. The bug lives silently in the data layer until a join or an assertion exposes it.

A second failure mode is bulk-insert scripts that use COPY or INSERT ... SELECT with coalesced defaults. COALESCE(source.coupon_id, 0) is a pattern that appears in ETL pipelines meant to handle missing upstream data, and it migrates directly into seed pipelines without scrutiny. Audit every COALESCE call in your seed SQL for this pattern — replace them with NULLIF(source.coupon_id, 0) where the source might legitimately carry zero as a sentinel, or leave the column NULL directly if the upstream field is absent.

Myths About Nullable FKs in Test Fixtures vs. Reference Data

Myth 1: "FK constraints will catch this." Only if the constraint is enforced, the parent table has no row with PK 0 (usually true, but not guaranteed in legacy schemas with explicit zero-ID sentinel rows), and the constraint isn't deferred. Many production Postgres schemas disable FK constraints for bulk loads and never re-enable them in the test environment. Myth 2: "This is a reference data problem, not a fixture problem." Reference data and test fixtures are different things — reference data is stable lookup content (country codes, status enums), while fixtures are the relational scaffolding your tests run against. Confusing them leads teams to validate reference data integrity while leaving fixture FK semantics unchecked. The tenant isolation gaps that emerge from shared seed pools are a related symptom of the same confusion.

Myth 3: "Randomness in seed data provides coverage." Randomly generating FK values — including the occasional zero — doesn't cover the nullable case meaningfully. What you need is intentional coverage: explicit factory states for "relationship present," "relationship absent (NULL)," and "relationship invalid (wrong ID)." Hypothesis can generate these states systematically with st.one_of(st.none(), st.integers(min_value=1)) for the FK field, which is far more useful than a random integer range that might or might not include zero.

Zero-as-null FK contamination is one of the quieter ways test data undermines suite reliability — no loud constraint violation, just wrong join results and assertions that pass for the wrong reason. The fix is a three-layer defense: Pydantic/factory_boy defaults that make None the only valid "absent" encoding, JSON Schema constraints that reject zero at the wire layer, and a Great Expectations suite that runs in CI as a persistent invariant. Start with the detection query against your current seed corpus — the scope of the problem usually surprises teams the first time they look.

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.