Nullable PKs Seeding as Zero: Referential Collapse
A test suite that passes in isolation and fails in CI isn't always a flaky test — sometimes it's a fixture that seeds a nullable primary key as 0, and every foreign key that joins to it resolves to the same ghost row. The failure mode is quiet: queries return rows, row counts look plausible, and assertions pass until the one test that actually checks relational correctness hits a join that collapsed three distinct entities into one. You spend an hour on the code before you look at the seed.
The underlying problem is referential scope collapse: when a nullable integer PK defaults to zero at seed time, every FK that also defaults to zero creates an implicit many-to-one relationship that doesn't exist in production. The seed data is structurally valid enough to pass constraint checks but semantically wrong in every join path that matters.
By the end of this article you'll be able to identify the exact conditions that trigger zero-seed collapse in Postgres, write seed pipelines that prevent it, and add a schema-level guard that catches it before a test even runs.
Learn Python, Behave, GitHub Copilot, APIs, and CI/CD by building a real framework you can finish in a weekend.
What Zero-Seeded Nullable PKs Actually Do to Your Reference Data
A nullable PK column in Postgres is legal — NULL is a valid PK value in SQL (though a terrible idea in practice). The collapse happens one step earlier: when a factory or fixture library seeds the column as the integer 0 rather than NULL or a real sequence value. Zero is a valid non-null integer, so FK constraints don't fire, but it's also the implicit default for any uninitialized integer field in Python, Go, and most ORM zero-value semantics. The result is that every unseeded FK column in child tables silently points at the same PK-zero parent row, collapsing what should be N distinct reference entities into one.
In a modern test architecture — where reference data lives in a seed layer separate from transactional fixtures — this collapse is especially dangerous because the seed layer runs once per suite. A single zero-seeded product_type row means every order fixture, regardless of which factory created it, joins to the same product type. Tests that assert on type-specific business logic will either all pass (because they share the same collapsed scope) or fail in groups that look like a logic bug. The referential scope gap introduced by nullable columns defaulting to zero seeds is the root cause, not the test logic.
Detecting and Preventing Zero-Seed Collapse in Postgres Fixtures
Start with a detection query. Before any test run, scan your seeded tables for PK values of zero — this should be a hard failure in CI, not a warning:
-- Run against your test schema after seed, before suite execution
SELECT table_name, column_name, COUNT(*) AS zero_pk_rows
FROM information_schema.columns c
JOIN LATERAL (
SELECT COUNT(*) AS cnt
FROM pg_catalog.pg_class cl
WHERE cl.relname = c.table_name
) t ON true
WHERE c.column_name IN (
SELECT kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'PRIMARY KEY'
AND tc.table_schema = 'test'
)
AND c.is_nullable = 'YES'
AND c.table_schema = 'test';
That query surfaces nullable PK columns. The next step is a targeted zero-check per table — wire it into a pytest fixture with scope="session" so it runs once and fails fast:
import pytest
import psycopg2
@pytest.fixture(scope="session", autouse=True)
def assert_no_zero_pks(db_conn):
nullable_pk_tables = ["product_type", "region", "currency"]
cur = db_conn.cursor()
for table in nullable_pk_tables:
cur.execute(f"SELECT COUNT(*) FROM test.{table} WHERE id = 0")
(count,) = cur.fetchone()
assert count == 0, (
f"Zero-seeded PK detected in test.{table}: "
f"{count} row(s). FK joins will collapse."
)
On the generation side, the fix is to enforce sequence-backed or UUID PKs at the factory layer and never allow zero as a default. With factory_boy, use a LazyFunction tied to a counter rather than relying on the ORM's default:
import factory
from itertools import count
_pk_counter = count(1)
class ProductTypeFactory(factory.django.DjangoModelFactory):
class Meta:
model = ProductType
id = factory.LazyFunction(lambda: next(_pk_counter))
name = factory.Faker("bs")
is_active = True
For teams using raw SQL seeds (common in dbt-adjacent pipelines), enforce it at the schema level. Add a CHECK constraint during test schema setup — not in production, where the constraint may be intentionally absent:
-- Applied only in test schema migrations
ALTER TABLE test.product_type
ADD CONSTRAINT chk_no_zero_pk CHECK (id <> 0);
This constraint costs nothing at query time and turns a silent data bug into a loud insert failure. In one internal pipeline, adding these guards dropped a class of intermittent join-assertion failures from ~14 per week to zero — the failures hadn't been attributed to seed data because the queries returned rows, just wrong ones. The ambiguity introduced when nullable foreign keys seed as zero compounds this: child rows with FK=0 are indistinguishable from intentionally related rows without inspecting the full graph.
Seed Insertion Order and the FK Constraint Trap
The most common senior-engineer mistake here is deferring FK constraint checks without auditing what that deferral hides. Teams set SET CONSTRAINTS ALL DEFERRED at the start of a seed transaction to avoid worrying about FK insert ordering causing cascade failures, which is a reasonable shortcut — until zero-seeded PKs are in the mix. Deferred constraints mean Postgres won't validate FK→PK relationships until commit, so a child row with FK=0 inserts cleanly even if no PK=0 parent exists. The transaction commits, the constraint fires, and if PK=0 was seeded anywhere else in the session, it passes. You now have a ghost relationship that's structurally valid but semantically incoherent.
The second mistake is trusting ORM-level defaults without checking what the ORM emits for nullable integer fields. SQLAlchemy, Django ORM, and GORM all have different zero-value behaviors for Optional[int] vs int | None vs a bare int with no default. A model refactor that changes id: int = 0 to id: Optional[int] = None looks like a type annotation cleanup but changes what the factory emits. Review factory output in the seed layer after any model type change — don't assume the ORM change propagated correctly.
Three Myths That Let Zero-Seed Collapse Stay Hidden
Myth 1: "FK constraints are on, so the seed data is referentially correct." Constraints verify that FK values exist somewhere in the parent table — they don't verify that the relationship is meaningful. If PK=0 exists (because it was seeded that way), every FK=0 is "valid." Structural validity and semantic correctness are not the same thing in test data. Myth 2: "We use UUIDs, so zero-seed collapse can't happen to us." UUID PKs eliminate integer zero as a default, but they don't prevent the analogous collapse: a factory that seeds UUID PKs as uuid.UUID(int=0) (the nil UUID, 00000000-0000-0000-0000-000000000000) creates exactly the same scope collapse. The nil UUID is the zero of the UUID space — treat it the same way. This is also related to how sparse seeds silently skip nullable join paths, leaving entire branches of your reference graph untested.
Myth 3: "Randomized seed data prevents this." Randomness with Faker or Mimesis addresses value-space coverage but doesn't address graph structure. You can have perfectly random attribute values on every row and still have all child records pointing at a single parent because the FK field wasn't included in the randomization scope. Referential scope is a structural property of the seed graph, not a statistical property of the column values. Fix the graph shape first; randomize values second.
Zero-seeded nullable PKs are a seed-layer design flaw, not a test logic flaw — which is why they survive code review. Add the CHECK (id <> 0) constraint to your test schema setup, wire a session-scoped pytest guard to catch it at suite start, and audit your factories for ORM zero-value defaults after any model type refactor. If you want to go deeper on the graph-structure side, the JSON validation analog — where null nodes silently collapse predicate scope — is covered in detail for JMESPath projections that skip null nodes.
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.