Seedfast

Seedfast

All posts

Circular Foreign Key Seed: Three Workarounds That Actually Run

By the Seedfast team ·

A circular foreign key seed is the case where two Postgres tables reference each other and your seed file has no valid first row to insert. You pick a parent, INSERT it, and Postgres replies with violates foreign key constraint. You flip the order. Same error, from the other direction. Every ordering fails the constraint check the moment the row hits disk.

This is a narrower problem than the generic foreign-key ordering one. The general FK case has a topological-sort answer — insert parents before children. The circular case doesn't have a sort answer; it's a different shape of fix. This article walks through the three SQL patterns Postgres actually supports for this — deferred constraints, a nullable back-edge with a two-phase UPDATE, and a data-modifying CTE — then shows what happens when you point Seedfast at the same schema, and what doesn't.

Key Takeaways#

  • A circular foreign key seed fails by default because Postgres checks each FOREIGN KEY constraint immediately after each row. With two tables that reference each other and both columns NOT NULL, no insertion order satisfies both checks at row-write time.
  • The canonical fix is DEFERRABLE INITIALLY IMMEDIATE on the constraint plus SET CONSTRAINTS ALL DEFERRED inside the seed transaction. Postgres then validates the constraints at COMMIT instead of after each row.
  • If one side of the cycle is genuinely optional, a nullable back-edge plus a two-phase INSERT … INSERT … UPDATE is simpler and doesn't change constraint semantics for normal app traffic.
  • Before reaching for any workaround, ask whether the cycle is intrinsic to the domain or an accident of the schema. Removing a back-edge that loses business meaning is the first case; removing one that only changes how queries are written is the second, and refactoring beats INITIALLY DEFERRED there.
  • When the schema permits a two-phase insert, Seedfast resolves the insert order automatically — no hand-written SQL. When the schema doesn't (strict NOT NULL on both sides with no deferral), no tool can; the deferred-constraint pattern in Workaround 1 stays the answer.

Why a circular foreign key seed fails on the first INSERT#

Take the simplest concrete case the brief in your head is probably already running on. Two tables. Each one references the other. Both back-edges are NOT NULL because the domain says they have to be:

CREATE TABLE organizations (
    id                BIGSERIAL PRIMARY KEY,
    name              TEXT NOT NULL,
    primary_owner_id  BIGINT NOT NULL,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE employees (
    id               BIGSERIAL PRIMARY KEY,
    full_name        TEXT NOT NULL,
    email            TEXT NOT NULL UNIQUE,
    organization_id  BIGINT NOT NULL,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE organizations
    ADD CONSTRAINT organizations_primary_owner_id_fkey
    FOREIGN KEY (primary_owner_id) REFERENCES employees(id);

ALTER TABLE employees
    ADD CONSTRAINT employees_organization_id_fkey
    FOREIGN KEY (organization_id) REFERENCES organizations(id);

Every employee belongs to an organization. Every organization has a primary owner who is one of its employees. Both halves are real, both halves are required, and the schema is fine — until the seed file runs.

The naive seed reaches for the parent table first:

BEGIN;
INSERT INTO organizations (id, name, primary_owner_id)
VALUES (1, 'Northwind Logistics', 1);

INSERT INTO employees (id, full_name, email, organization_id)
VALUES (1, 'Sam Patel', 'sam.patel@northwind.example', 1);
COMMIT;

The output:

BEGIN
psql:/work/naive-insert.sql:9: ERROR:  insert or update on table "organizations" violates foreign key constraint "organizations_primary_owner_id_fkey"
DETAIL:  Key (primary_owner_id)=(1) is not present in table "employees".
psql:/work/naive-insert.sql:12: ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK

The reason is one sentence: by default, Postgres runs each FOREIGN KEY check at the moment a row is inserted, not at COMMIT. The organization row needs an existing employee 1, and that row doesn't exist yet. Flipping the order — employee first — produces the symmetric error from employees_organization_id_fkey because the organization the employee is supposed to belong to doesn't exist either. The cycle has no entry point under the default check timing. That timing is what each of the workarounds below changes, in different ways.

Is your circular FK actually circular, or is it a schema smell?#

The competitor articles that rank for this query mostly skip this question and jump straight to INITIALLY DEFERRED. It's worth pausing on, because the answer changes which workaround is the right one — or whether you need one at all.

There's a useful distinction between intrinsic cycles and accidental ones.

An intrinsic cycle is one where removing either back-edge changes the meaning of the data. The organizations ↔ employees cycle above is intrinsic: an organization without a designated primary owner is a different domain object, and an employee with no organization is too. Both directions of the relationship encode something the business actually cares about, and the cycle is the honest expression of that — it's also the case where what referential integrity actually guarantees starts to matter, because the cycle is the constraint, not just an ordering puzzle. For these, you have to pick a workaround — the cycle isn't going away.

An accidental cycle is one where removing the back-edge only changes how queries are written. A common accidental shape is a users.created_by_user_id self-reference plus a created_users denormalized column on the parent — the same fact represented twice, in opposite directions, "for query performance". Another is a parent table with a latest_<child>_id pointer — the child already references the parent, and the back-pointer exists so a join can be skipped. In both cases, the cycle is a cache, not a fact. The fix is to drop the cached column and write the join.

A practical heuristic: if you remove the back-edge and the team has to re-explain a business rule (every organization must have a primary owner), the cycle is intrinsic. If you remove it and the team only has to rewrite one query, it's accidental, and the schema is the bug.

For accidental cycles, the cheapest workaround is the migration that removes the cycle. A junction table dissolves the back-edge into a join. Moving one column to a third table — for example, an organization_owners table with organization_id and employee_id — preserves the data without forcing either parent table to know about the other. These are migrations, not seed-script tricks, and they pay back every time anyone touches the schema afterwards.

For intrinsic cycles, keep reading.

In practice, cycles often arrive after the fact: a migration last sprint added a back-edge — a created_by_user_id on users, a latest_invoice_id on customers for a dashboard query — and the seed file that was fine yesterday now fails. This is a more common origin story than the schema looking circular from day one.

Workaround 1: deferred constraints inside the transaction#

The canonical Postgres answer is to tell the constraint to defer its check to COMMIT. Two pieces:

  1. The constraints must be declared DEFERRABLE. The default is NOT DEFERRABLE and must be changed at the constraint level, either in the original ALTER TABLE or in a follow-up migration.
  2. Inside the seed transaction, SET CONSTRAINTS ALL DEFERRED flips the runtime behavior so checks run at COMMIT instead of after each row.

If your migrations live in an ORM (Prisma, Drizzle, TypeORM), this redeclaration is a raw-SQL migration step regardless — the DEFERRABLE flag isn't expressible in their schema DSLs.

-- Step A: redeclare the constraints as DEFERRABLE INITIALLY IMMEDIATE.
-- Default-immediate means normal app traffic still gets row-by-row checks.

BEGIN;

ALTER TABLE organizations
    DROP CONSTRAINT organizations_primary_owner_id_fkey;
ALTER TABLE organizations
    ADD CONSTRAINT organizations_primary_owner_id_fkey
    FOREIGN KEY (primary_owner_id) REFERENCES employees(id)
    DEFERRABLE INITIALLY IMMEDIATE;

ALTER TABLE employees
    DROP CONSTRAINT employees_organization_id_fkey;
ALTER TABLE employees
    ADD CONSTRAINT employees_organization_id_fkey
    FOREIGN KEY (organization_id) REFERENCES organizations(id)
    DEFERRABLE INITIALLY IMMEDIATE;

COMMIT;

-- Step B: the seed transaction itself.
BEGIN;
SET CONSTRAINTS ALL DEFERRED;

INSERT INTO organizations (id, name, primary_owner_id)
VALUES (1, 'Northwind Logistics', 1);

INSERT INTO employees (id, full_name, email, organization_id)
VALUES (1, 'Sam Patel', 'sam.patel@northwind.example', 1);

INSERT INTO organizations (id, name, primary_owner_id)
VALUES (2, 'Cascadia Foods', 2);

INSERT INTO employees (id, full_name, email, organization_id)
VALUES (2, 'Mira Okafor', 'mira.okafor@cascadia.example', 2);

COMMIT;

Run against the schema above:

BEGIN
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
COMMIT
BEGIN
SET CONSTRAINTS
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
COMMIT
  table_name   | count 
---------------+-------
 organizations |     2
 employees     |     2
(2 rows)

Two trade-offs to know. First, deferred constraints cost more memory: Postgres has to track the pending checks until COMMIT, and the bigger the transaction the higher the queue. For seed files that's usually fine; for million-row bulk loads it's worth measuring. The Cybertec write-up at position 1 of the SERP for this query makes the same point. Second, INITIALLY IMMEDIATE is the safer default than INITIALLY DEFERRED: it leaves normal application transactions on row-by-row checking and only relaxes that for sessions that explicitly opt in with SET CONSTRAINTS ALL DEFERRED. The seed file opts in; the live app doesn't.

Workaround 2: nullable foreign key + two-phase insert#

If your business rule actually allows one side of the cycle to be temporarily empty — for example, an organization can exist for a few rows of bookkeeping before its primary owner is decided — make that column NULL-able and break the cycle in three statements:

ALTER TABLE organizations
    ALTER COLUMN primary_owner_id DROP NOT NULL;

BEGIN;

INSERT INTO organizations (id, name, primary_owner_id)
VALUES
  (1, 'Northwind Logistics', NULL),
  (2, 'Cascadia Foods', NULL);

INSERT INTO employees (id, full_name, email, organization_id)
VALUES
  (1, 'Sam Patel', 'sam.patel@northwind.example', 1),
  (2, 'Mira Okafor', 'mira.okafor@cascadia.example', 2);

UPDATE organizations SET primary_owner_id = 1 WHERE id = 1;
UPDATE organizations SET primary_owner_id = 2 WHERE id = 2;

COMMIT;

The trade-off is the obvious one: this requires the column to be nullable. If the domain says every organization must have a primary owner from the moment of its creation, you can't do this — the column has to stay NOT NULL, and you're back to Workaround 1. Don't drop NOT NULL purely to get the seed file to run, because that quietly weakens a business rule that the rest of the application relies on.

Where this pattern earns its keep is when one side of the cycle is genuinely optional in the domain — a manager_id that's nullable for the CEO's row, a parent_category_id that's nullable for the root category. In those cases the schema already allows it, the seed file doesn't have to fight DEFERRABLE, and the result reads like ordinary code.

Workaround 3: data-modifying CTE for one-shot inserts#

Postgres's WITH … RETURNING lets you chain the INSERTs and the back-fill UPDATE into a single statement. The orgs row is written with primary_owner_id = NULL (so the FK is satisfied trivially), the employees row references the just-inserted org, and the final UPDATE sets the back-edge — all in one statement.

WITH inserted_orgs AS (
    INSERT INTO organizations (id, name, primary_owner_id)
    VALUES (1, 'Northwind Logistics', NULL),
           (2, 'Cascadia Foods', NULL)
    RETURNING id
),
inserted_employees AS (
    INSERT INTO employees (id, full_name, email, organization_id)
    VALUES (1, 'Sam Patel', 'sam.patel@northwind.example', 1),
           (2, 'Mira Okafor', 'mira.okafor@cascadia.example', 2)
    RETURNING id
)
UPDATE organizations o
SET primary_owner_id = e.id
FROM inserted_employees e
WHERE o.id = e.id;

Two caveats. First, this still needs primary_owner_id to be nullable at the moment the parent rows are written — it doesn't get you out of Workaround 2's schema requirement, it just folds the three statements into one. Second, it's harder to read than the two-phase version, and the readability cost compounds for seed files with hundreds of rows. CTEs work best when the seeding is genuinely one-shot — for example, an idempotent migration that bootstraps a small set of reference rows.

There's also a fourth pattern in the wild — ALTER TABLE … DISABLE TRIGGER ALL to bypass FK checks during a sync. The rubyrep gist near the top of the SERP recommends it for replication. Don't use it in a seed file. Disabling triggers lets bad data in and bypasses domain constraints, which is the failure mode the seed file is supposed to catch in the first place.

When the seeder picks the order for you#

The three patterns above are variations on the same idea: tell Postgres that the constraint is satisfied at COMMIT, not after each row, and write the inserts in two phases — parents (or shells) first, children second, back-references third. If the schema permits any of those, a seeder that reads the schema can detect the cycle and apply the pattern without you writing the SQL yourself.

That word "permits" is doing real work. Here's what happens when Seedfast runs against the strict-NOT-NULL schema we started with — both back-edges NOT NULL, neither constraint declared DEFERRABLE:

[2026-05-01T13:07:57+02:00] INFO: Seeding started
[2026-05-01T13:08:06+02:00] INFO: Tables in scope: public.organizations, public.employees
[2026-05-01T13:08:06+02:00] INFO: Planned: 8 records across 2 tables
[2026-05-01T13:08:06+02:00] INFO: Auto-approving plan (scope provided)
[2026-05-01T13:08:07+02:00] INFO: Seeding table public.organizations (1/2)
[2026-05-01T13:08:07+02:00] INFO: Seeding table public.employees (2/2)
[2026-05-01T13:09:31+02:00] ERROR: Failed table public.organizations: Failed: delta=0 < expected=2 (baseline=0, final=0)
[2026-05-01T13:09:31+02:00] ERROR: Failed table public.employees: Failed: delta=0 < expected=6 (baseline=0, final=0)
[2026-05-01T13:09:31+02:00] INFO: Seeding completed: 0/2 tables succeeded, 0 rows, 95.67s
[2026-05-01T13:09:31+02:00] WARN: 2 tables failed

It fails. So does every other seeder, because no tool can violate a NOT NULL immediate-checked constraint that Postgres itself enforces row by row. Insert-order resolution is the seeder's job; constraint enforcement is the database's job, and that line doesn't move. The same run on a DEFERRABLE INITIALLY IMMEDIATE variant of the schema also failed, because Seedfast doesn't currently issue SET CONSTRAINTS ALL DEFERRED inside its transaction — that part is left for the operator.

Now drop NOT NULL from organizations.primary_owner_id — the nullable variant we used in Workaround 2 — and run the same scope:

[2026-05-01T13:11:58+02:00] INFO: Seeding started
[2026-05-01T13:12:03+02:00] INFO: Tables in scope: public.organizations, public.employees
[2026-05-01T13:12:03+02:00] INFO: Planned: 8 records across 2 tables
[2026-05-01T13:12:03+02:00] INFO: Auto-approving plan (scope provided)
[2026-05-01T13:12:04+02:00] INFO: Seeding table public.organizations (1/2)
[2026-05-01T13:12:04+02:00] INFO: Seeding table public.employees (2/2)
[2026-05-01T13:12:32+02:00] INFO: Table public.organizations completed: 2 rows in 27.144s
[2026-05-01T13:12:32+02:00] INFO: Table public.employees completed: 6 rows in 27.144s
[2026-05-01T13:12:32+02:00] INFO: Seeding completed: 2/2 tables succeeded, 8 rows, 35.16s

Two organizations, six employees, in cyclic order, with each organization's primary owner one of its own employees — and no hand-written two-phase INSERT. When the schema gives Seedfast room — one side nullable, or DEFERRABLE INITIALLY DEFERRED declared and accepted at runtime — Seedfast resolves the order automatically, and the operator writes zero seed-side SQL. The other case — strict NOT NULL on both sides with no deferral — no tool can solve in software; that's a schema decision, not a tooling one.

If your seed file keeps running into this and your schema permits the two-phase write, see seedfa.st for what that looks like end-to-end on your own database. If it doesn't permit it, the deferred-constraint pattern in Workaround 1 is still the cleanest answer, and it's worth rolling once into the migration that adds the back-edge so the next person to write a seed file doesn't repeat this.

Related guides#