Foreign key constraint failed on the field

Not specified by the original author.

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.

Not specified by the original author.

TL;DR

Find the duplicate or missing foreign key behind the Cachet SQLSTATE 23000 on incident creation: usually a component or status ID that does not exist in this Cachet install, so use IDs from the Cachet API's own component and status lists. Prove it on one test incident before rolling it to the rest of the affected status page, because this fault repeats identically wherever the same condition holds and a fleet-wide or batch-wide first attempt multiplies the damage when the diagnosis is wrong.

Cachet incident creation failed "SQLSTATE 23000 integrity constraint violation" error

Use this when

• You see this exact error or symptom: Cachet incident creation failed "SQLSTATE 23000 integrity constraint violation" error.
• The failure is in the status page provider named in the title, and it repeats on the same action rather than happening once and clearing.
• Other items on the same path are working, so the path itself is up and this specific condition is the blocker.

Not for this skill when

• A different error code, message, or tool is involved. Work that error's own skill, because the fix here targets this specific condition.
• The whole service or platform is down. Restore the service first; this skill fixes the named condition, not an outage.
• You cannot identify one affected item to test on. Find one first, because an untested fix rolled out broadly is how this fault gets worse.

Steps

1. Confirm the fault on one test incident first and capture the evidence before changing anything: check the provider's admin console error and activity log for the page, plus the public page as a logged-out visitor sees it, and record the exact error text, the record or host involved, and the time it happened. Verify: you can point to the exact error and the exact item it belongs to, not just the symptom
2. Find the duplicate or missing foreign key behind the Cachet SQLSTATE 23000 on incident creation: usually a component or status ID that does not exist in this Cachet install, so use IDs from the Cachet API's own component and status lists. Verify: the specific fault named in the title no longer occurs on that item when you repeat the action that produced it
3. Make the operation idempotent: look the record up by its stable key before creating, adopt or update the existing record when it is the same entity, and log which path was taken. Verify: the guard or check is in place and is visible in the run, log, or configuration you changed
4. Retest end to end: repeat the failed action on a test incident or draft and confirm the public page reflects it without notifying subscribers unintentionally. Verify: the retest completes without the original error and the resulting state matches what a manual check shows
5. Roll the fix out to the rest of the affected status page in the same way, watching the first few for the same error, and note the root cause and the change in the run or incident record. Verify: the remaining items complete without the error recurring, or the ones that still fail are listed individually with their own evidence

Variant phrasings

SQLSTATE 23000 integrity constraint violation fix

The verbatim error or code phrasing for this fault in the status page provider named in the title; the fix is the same one in the steps above.

Cachet incident creation failed "SQLSTATE 23000 integrity constraint

The full symptom phrasing as it appears in the console, log, or report where this failure surfaces.

how to fix Cachet incident creation failed "SQLSTATE 23000 integrity

The how-to phrasing for the same fault; start with the TL;DR fix, then work the steps in order.

Compatibility: Atlassian Statuspage, Instatus, Better Stack, Cachet, Status.io, Hund, Freshstatus, Statuspal, UptimeRobot, StatusCake, All Quiet, FireHydrant, Squadcast, xMatters, Grafana Incident and PagerDuty as named in the title.

Why it happens

This fault happens because create-style operations are not idempotent in most of these systems, so a retry or a pre-existing record surfaces as a hard conflict instead of a no-op. In this case the condition is the one named in the title, in the status page provider named in the title: the receiving or acting side reports the failure faithfully, but the underlying state (a record, a setting, a credential, a file, or a mapping) does not match what that action assumes. Fixing the state, as in step 2, clears it; retrying the action without changing the state returns the same error every time, which is why this failure looks stubborn until the state itself is corrected.

Provenance

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

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

Thread query: Cachet incident creation failed "SQLSTATE 23000 integrity constraint violation" error

TL;DR: Find the duplicate or missing foreign key behind the Cachet SQLSTATE 23000 on incident creation: usually a component or status ID that does not exist in this Cachet install, so use IDs from the Cachet API's own component and status lists. Prove it on one test incident before rolling it to the rest of the affected status page, because this fault repeats identically wherever the same condition holds and a fleet-wide or batch-wide first attempt multiplies the damage when the diagnosis is wrong.

Not specified by the original author.

TL;DR: Look up the parent record before you create the child. If the parent lookup misses, return a clear not-found message instead of letting the database throw P2003. The error means the id you are linking to does not exist.

Foreign key constraint failed on the field: `authorId`

Steps

1. Read the field name in the error. authorId tells you the relation that failed: the author you referenced does not exist.

2. Verify the parent exists before creating the child:

   const author = await prisma.user.findUnique({ where: { id: input.authorId } })
   if (!author) {
     return { ok: false, message: 'Author not found.' }
   }
   await prisma.post.create({ data URIs { title: input.title, authorId: input.authorId } })

   Expected: a bad author id returns your clean message; a good one creates the post.

3. Still catch P2003 as a backstop for races (the parent can be deleted between your check and your write):

   } catch (e) {
     if (e instanceof PrismaClientKnownRequestError && e.code === 'P2003') {
       return { ok: false, message: 'The linked record no longer exists.' }
     }
     throw e
   }

   Expected: concurrent deletes produce a friendly message, not a 500.

When to use

• The exact code is P2003 on a create, update, or connect that references another record
• The referenced id comes from user input, a URL param, or another service

When not to use

• P2002: that is a duplicate value, not a missing parent
• You are deleting a parent with children: that is a cascade / restrict policy question, set onDelete in the schema

Compatibility

Prisma Client 4.x through 6.x. All relational providers.

Variant phrasing: P2003 on connect: { id } in a nested write

Same fix: verify the id exists before the nested write, or catch P2003 around it.

Variant phrasing: P2003 in a seed script

Seeds run in order; the parent seed must run (and commit) before the child seed. Check your seed order.

Why it happens

The foreign key in the database guarantees the child always points at a real parent row. When the app sends an id that is not there (typo, stale client state, deleted parent), the database rejects the write and Prisma reports P2003. Checking first turns a database rejection into an application-level message.

Edge cases

• onDelete: Cascade vs Restrict: deleting a parent with children fails differently; choose the policy your domain needs.
• UUID vs integer ids: a malformed id string can fail parsing before it ever reaches the constraint check.
• Multi-tenant apps: the parent may exist but belong to another tenant; scope the lookup by tenant, not just by id.

A fuel quota management backend hit P2003 when creating vehicle records that referenced a user id that did not exist. The fix merged into the codebase: before creating the child record, look up the parent with findUnique, and return a clear 'user not found' error when the lookup misses instead of letting the database reject the write. After the change, bad references produce a clean application-level message and valid writes proceed. Source: https://github.com/im-ushan-ikshana/fuel-quota-management/blob/HEAD/backend/docs/PRISMAERRORHANDLING.md