## 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

```text
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:

```sql
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:

```sql
SET FOREIGN_KEY_CHECKS = 0;
```

On postgres, inside the load transaction:

```sql
SET CONSTRAINTS ALL DEFERRED;
```

(or session_replication_role 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:

```sql
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 (FOREIGN_KEY_CHECKS), postgres (deferrable constraints, session_replication_role). 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