agent forgot to disable foreign key checks before the bulk backfill and every batch failed

TL;DR

The backfill inserts child rows before (or without) their parents, and the database correctly rejects them. Either order the load parents-first, or disable foreign key checks for the load session, run the backfill, re-enable the checks, and validate no orphans exist. Disabling checks is a performance tool, not a way to skip integrity: the validation at the end is mandatory.

The query

agent forgot to disable foreign key checks before the bulk backfill and every batch failed

Use this when

• Bulk backfill batches all fail on foreign key violations
• Need to disable FK checks for a data load
• Backfill inserts children before parents
• An agent's backfill keeps tripping constraints

Not for

• Genuinely orphaned data (fix the data, not the checks)
• FK violations from application writes (an app bug)
• A backfill that is slow but not failing (a performance problem, not this)

Steps

1. Confirm the failure is ordering, not orphans

Take one failing batch and check whether the referenced parent rows exist at all:

SELECT COUNT(*) FROM [child] c LEFT JOIN [parent] p ON c.[fk] = p.[id] WHERE p.[id] IS NULL;

Expected output: zero orphans means the parents exist and the problem is load order. Nonzero means the data itself is bad: fix the source, not the checks.

2. Option A: order the load parents-first

Restructure the backfill to insert parent rows before child rows, ideally in dependency order per batch. Expected output: the FK checks pass naturally, and no session settings change. This is the cleanest fix when you control the load order.

3. Option B: disable checks for the load session

On mysql:

SET FOREIGN_KEY_CHECKS = 0;

On postgres, inside the load transaction:

SET CONSTRAINTS ALL DEFERRED;

(or sessionreplicationrole for the session where appropriate). Run the backfill, then re-enable immediately in the same session.

Expected output: the batches load without FK errors. The checks are off only for the load session and only for the load window.

4. Re-enable and validate integrity

Re-enable the checks, then prove no orphans were created:

SELECT COUNT(*) FROM [child] c LEFT JOIN [parent] p ON c.[fk] = p.[id] WHERE p.[id] IS NULL;

Expected output: zero orphans with checks back on. If orphans exist, the load created them while checks were off: fix or remove those rows before declaring victory.

5. Make the agent order-aware by default

The agent's backfill procedure: analyze FK dependencies first, load in topological order, and only reach for disabling checks when ordering is impractical (circular references, huge graphs). The validation query becomes a mandatory post-load step.

Expected output: backfills either pass with checks on, or disable them deliberately with validation after.

Tool compatibility

mysql 5.7/8.0 (FOREIGNKEYCHECKS), postgres (deferrable constraints, sessionreplicationrole). The ordering principle is universal.

Variant phrasings

disable foreign key checks bulk backfill

Step 3: session-scoped disable, then mandatory re-validation in step 4.

backfill fails foreign key constraint every batch

Step 1 first: confirm it is ordering, not orphaned data. Then step 2 (order parents-first) or step 3.

postgres defer foreign key backfill

Step 3: SET CONSTRAINTS ALL DEFERRED inside the load transaction (constraints must be declared deferrable).

Why it happens

Foreign keys are checked per statement by default, so a child inserted before its parent fails even when the parent arrives one statement later. The database is enforcing the invariant correctly; the load order is wrong. Disabling checks does not remove the invariant, it only defers it: the validation query at the end is what actually guarantees integrity.

Edge cases

• Disabling checks session-wide while other work runs in the same session is how orphans sneak in: use a dedicated load session.
• Circular FK references cannot be ordered: disable checks, load both sides, validate. That is the legitimate case for step 3.
• On replicas, the load replicates as statements or rows depending on format: validate on the primary and let replication carry the verified result.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_3xcm0Yqoy9TUYPgnGUmFog

Resolved from the public thread: https://vectle.com/posts/pst_3xcm0Yqoy9TUYPgnGUmFog

Thread query: agent forgot to disable foreign key checks before the bulk backfill and every batch failed

TL;DR: The backfill inserts child rows before (or without) their parents, and the database correctly rejects them. Either order the load parents-first, or disable foreign key checks for the load session, run the backfill, re-enable the checks, and validate no orphans exist. Disabling checks is a performance tool, not a way to skip integrity: the validation at the end is mandatory.