P2003 Foreign key constraint failed

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

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: P2002 is not a bug in Prisma, it is your database doing its job. Catch PrismaClientKnownRequestError with code P2002 and return a friendly duplicate message, or switch the write to upsert so repeats update instead of crashing.

Unique constraint failed on the fields: (`email`)

Steps

1. Catch the error where you create the record:

   import { PrismaClientKnownRequestError } from '@prisma/client/runtime/library'
   
   try {
     await prisma.user.create({ data URIs { email: '[user email]', name: 'Ada' } })
   } catch (e) {
     if (e instanceof PrismaClientKnownRequestError && e.code === 'P2002') {
       return { ok: false, message: 'That email is already registered.' }
     }
     throw e
   }

   Expected: submitting a duplicate email returns your friendly message, not a 500.

2. Or make the write idempotent with upsert when repeats should update:

await prisma.vehicle.upsert({ where: { plateNumber: 'ABC-123' }, update: { mileage: 90000 }, create: { plateNumber: 'ABC-123', mileage: 90000 }, })

   Expected: running the same import twice leaves one record with the latest values.

3. Check `e.meta.target` to name the exact field in your message.
   Expected: `meta.target` lists the fields from the error, for example `['email']`.

## When to use
- The exact code is P2002 on a create or update against a `@unique` field
- Signup, invite, or import flows where duplicates are a normal user action

## When not to use
- P2003: that is a foreign key problem, the parent record is missing
- You actually want duplicates allowed: drop the `@unique` attribute and migrate

## Compatibility
Prisma Client 4.x through 6.x. All providers.

### Variant phrasing: `Unique constraint failed on the fields: (`phone`)`
Same fix; read the field list in the message to know which constraint fired.

### Variant phrasing: P2002 on a composite `@@unique([a, b])`
The combination must be unique, not each field alone. Either change the data or change the constraint.

## Why it happens
The `@unique` attribute creates a real database unique index. When two writes target the same value, the database rejects the second one and Prisma surfaces it as P2002. The fix belongs in application code because only the app knows whether a duplicate is a user error or an idempotent retry.

## Edge cases
- Race conditions: a find-then-create check still races under concurrency; the P2002 catch is the reliable guard.
- Case sensitivity: `[recipient email]` and `[recipient email]` are different values to most databases; normalize case before writing if you want them treated as one.
- Soft deletes: a unique index still covers soft-deleted rows; use a partial index or composite unique with the deleted flag if you need reuse.

A fuel quota management backend hit P2002 when registering users with an email that already existed. The fix merged into the codebase: wrap the create call, catch PrismaClientKnownRequestError with code P2002, and return a clear 'email already exists' response instead of a 500. The same codebase uses upsert for vehicle records so repeated imports update instead of crashing. After the change, duplicate submissions get a clean 409-style message and idempotent imports work. Source: https://github.com/im-ushan-ikshana/fuel-quota-management/blob/HEAD/backend/docs/PRISMAERRORHANDLING.md