Prisma deleteMany: Foreign key constraint failed (P2003)

TL;DR#

prisma.profile.deleteMany() fails with P2003 because rows in Project still reference those profiles, and the foreign key Project_profile_email_fkey refuses to let the parent go first. Fix it by deleting the children before the parents, or by setting onDelete: Cascade and running a migration so the constraint in PostgreSQL changes. Removing onDelete: NoAction does not help: a required relation then defaults to Restrict, which blocks the delete the same way.

The error, as posted in the original Stack Overflow question:

Invalid `prisma.profile.deleteMany()` invocation in
clear.js:6:24
 
  3 const prisma = new PrismaClient();
  4
  5 (async () => {
→ 6   await prisma.profile.deleteMany(
  Foreign key constraint failed on the field: `Project_profile_email_fkey (index)`

The script in the question deletes every Profile first and every Project second. The second line never runs, because the first one is rejected.

The scenario was reproduced on 1 October 2026 with Prisma ORM 7, the prisma-client generator and the @prisma/adapter-pg driver adapter, against a local Prisma Postgres server (PostgreSQL 17) started with prisma dev, on Node.js 26. With that stack the same delete returns the same code with different wording. Two profiles and two projects were seeded, then the question's order was run:

before: profiles=2 projects=2
code: P2003
meta: {"modelName":"Profile","driverAdapterError":{"name":"DriverAdapterError","cause":{"originalCode":"23503","originalMessage":"update or delete on table \"Profile\" violates foreign key constraint \"Project_profile_email_fkey\" on table \"Project\"","kind":"ForeignKeyConstraintViolation","constraint":{"index":"Project_profile_email_fkey"}}}}
Foreign key constraint violated on the constraint: `Project_profile_email_fkey`
after: profiles=2 projects=2

Both wordings carry the code P2003. The Prisma error reference documents P2003 as "Foreign key constraint failed on the field", the wording in the question. Note the last line: nothing was deleted. Neither profile was removed, including the one that had no projects, so the tables were left exactly as they were.

Why it happens#

With a relational database, Prisma uses the foreignKeys relation mode by default: every @relation becomes a real foreign key constraint, created by Prisma Migrate or db push. This is the SQL that prisma migrate dev generated for the question's relation in the reproduction:

ALTER TABLE "Project" ADD CONSTRAINT "Project_profile_email_fkey" FOREIGN KEY ("profile_email") REFERENCES "Profile"("email") ON DELETE NO ACTION ON UPDATE CASCADE;

So the name in backticks, Project_profile_email_fkey, is the name of that constraint, not a column. When a statement removes a Profile row whose email is still stored in some Project.profile_email, PostgreSQL raises SQLSTATE 23503 (the originalCode in the output above) and Prisma maps it to P2003. The Prisma documentation does not explain the (index) suffix in the older wording. In the Prisma ORM 7 output the same name appears under constraint.index in meta.

The referential actions page describes NoAction on PostgreSQL as allowing the check "to be deferred until later in the transaction". The PostgreSQL foreign key documentation adds the condition: deferral only happens when constraint checking is deferred. The constraint above has no DEFERRABLE clause, and pg_constraint reported condeferrable: false for it in the test. The SET CONSTRAINTS reference states that a NOT DEFERRABLE constraint is always IMMEDIATE, and that immediate constraints are checked at the end of each statement. In practice, then, NO ACTION behaves like RESTRICT here: the check runs at the end of the DELETE statement, and the delete fails.

The highest-scored answer gets the essentials right: switch NoAction to Cascade, then, in the answer's own edit, "Make sure to update your database as well with the new constraint". That second half is the step people skip.

Fix#

Pick one of the two options. Option 1 changes only the order of your queries. Option 2 changes what deleting a profile means everywhere in your application.

Option 1: delete the children first#

1. Delete Project rows before Profile rows, in one sequential $transaction so that either both deletes happen or neither does:

await prisma.$transaction([
  prisma.project.deleteMany({}),
  prisma.profile.deleteMany({}),
]);

The CRUD page uses the same pattern and orders the parent table last. In the reproduction, this ran against the unchanged NoAction schema:

before: profiles=2 projects=2
deleteMany succeeded
after: profiles=0 projects=0

Option 2: cascade the delete in the database#

1. Change the referential action on the relation field in Project:

model Profile {
  id       String    @id @default(cuid())
  email    String    @unique
  projects Project[]
}
 
model Project {
  id            String  @id @default(cuid())
  profile       Profile @relation(fields: [profile_email], references: [email], onDelete: Cascade)
  profile_email String
}

2. Create and apply a migration so that PostgreSQL gets the new constraint:

npx prisma migrate dev --name cascade_project_profile

The generated migration.sql drops the old constraint and recreates it with ON DELETE CASCADE:

-- DropForeignKey
ALTER TABLE "Project" DROP CONSTRAINT "Project_profile_email_fkey";
 
-- AddForeignKey
ALTER TABLE "Project" ADD CONSTRAINT "Project_profile_email_fkey" FOREIGN KEY ("profile_email") REFERENCES "Profile"("email") ON DELETE CASCADE ON UPDATE CASCADE;

3. Regenerate Prisma Client. The Prisma ORM 7 migrate dev reference notes that the command no longer runs prisma generate for you:

npx prisma generate

4. Apply the same migration to staging and production with prisma migrate deploy. Until that runs, those databases keep the old constraint and keep returning P2003:

npx prisma migrate deploy

Verify the fix#

A run without an error does not prove that the children were removed: the question's script deletes Project rows itself on its second line. To check that the cascade did the work, call only the parent delete and count both tables before and after. This is the branch of the reproduction script that did it (the seed created two profiles and two projects):

async function counts(label: string) {
  const profiles = await prisma.profile.count();
  const projects = await prisma.project.count();
  console.log(`${label}: profiles=${profiles} projects=${projects}`);
}
 
await counts("before");
const r = await prisma.profile.deleteMany({});
console.log("profile.deleteMany count:", r.count);
console.log("deleteMany succeeded");
await counts("after");

After the migration in Option 2, the script printed:

before: profiles=2 projects=2
profile.deleteMany count: 2
deleteMany succeeded
after: profiles=0 projects=0

Both projects disappeared even though no project.deleteMany call ran. Then confirm what the database holds, rather than what schema.prisma says:

SELECT conname, pg_get_constraintdef(oid) AS def
FROM pg_constraint
WHERE conname = 'Project_profile_email_fkey';

After the cascade migration, the query (run through pg from Node.js) returned:

[
  {
    conname: 'Project_profile_email_fkey',
    def: 'FOREIGN KEY (profile_email) REFERENCES "Profile"(email) ON UPDATE CASCADE ON DELETE CASCADE'
  }
]

With NoAction in place, the same query returned only ON UPDATE CASCADE. NO ACTION is the default ON DELETE action in the PostgreSQL documentation, and pg_get_constraintdef did not print it, so a definition with no ON DELETE clause means a parent delete is refused while children still exist. If you prefer psql, the \d table description guide shows how to list a table's foreign keys.

Changes that leave P2003 in place#

Four tweaks were tested on the same data. Each one still failed, and each failure explains part of the mechanism.

Editing schema.prisma without migrating. With onDelete: Cascade in the schema and a freshly generated client, but no migration, the parent delete still failed with the same 23503 cause, and the counts stayed at profiles=2 projects=2. The client does not carry the referential action; the constraint does. The relation mode page says it plainly: foreign keys are created when you apply the schema with Prisma Migrate or db push.

Deleting onDelete: NoAction from the relation. The defaults table gives Restrict for onDelete on mandatory relations, and SetNull only on optional ones. The migration created for the bare relation confirmed it:

ALTER TABLE "Project" ADD CONSTRAINT "Project_profile_email_fkey" FOREIGN KEY ("profile_email") REFERENCES "Profile"("email") ON DELETE RESTRICT ON UPDATE CASCADE;

The parent delete then failed with P2003 again.

Wrapping the original order in $transaction. Running profile.deleteMany before project.deleteMany inside one sequential transaction failed with P2003, with both tables unchanged. As explained above, the constraint is not deferrable, so the order inside the transaction is what matters.

Switching to relationMode = "prisma". In this mode Prisma drops the foreign keys and enforces relations itself. On PostgreSQL, prisma validate rejected the question's relation outright:

error: Error validating: Invalid referential action: `NoAction`. Allowed values: (`Cascade`, `Restrict`, `SetNull`). `NoAction` is not implemented for Postgres when using `relationMode = "prisma"`, you could try using `Restrict` instead. Learn more at https://pris.ly/d/relation-mode

With Restrict instead, the delete was still blocked, now with a different code, P2014:

code: P2014
The change you are trying to make would violate the required relation 'ProfileToProject' between the `Project` and `Profile` models.

SetNull was not tested. According to the referential actions page it only works on optional relations (Profile? and String?), and it leaves projects without a profile instead of deleting them, which is a data model decision rather than a fix.

The lower-scored answer on the question blames the lowercase prisma.profile. That is not the cause: the reproduction calls prisma.profile.deleteMany with exactly that casing, and it succeeds once the children are gone.

The question's schema does not validate as posted#

The Project relation references email, but the posted Profile model has no email field. Prisma ORM 7 refuses it before any query runs:

Error: Prisma schema validation - (validate wasm)
Error code: P1012
error: Error validating: The argument `references` must refer only to existing fields in the related model `Profile`. The following fields do not exist in the related model: email

Adding email String alone is not enough either. Without @unique, validation failed with "The argument references must refer to a unique criterion in the related model", so the reproduction declares email String @unique, as in the Option 2 schema.

Cascade is a rule for every delete#

onDelete: Cascade is not a flag for a cleanup script. Once migrated, every deletion of a profile, from any code path, removes that profile's projects. If you only need an empty database between test runs, keep the relation strict and use Option 1, or the TRUNCATE ... CASCADE approach on the CRUD page. If you want to remove the whole schema instead of the rows, the guide to dropping all tables safely covers DROP SCHEMA public CASCADE and its guards. When you hit the same failure from a raw SQL client rather than Prisma, the PostgreSQL 23503 guide covers both directions of the error, including the "Key is still referenced" delete case.

If the migration in step 2 fails instead, the next error is in Prisma Migrate, not in the delete. On Supabase's pooled port, prisma migrate dev hangs after loading the schema (in 2022, when that port ran PgBouncer, it failed with a bouncer config error instead), and on Heroku Postgres it can stop with P3014, permission denied to create database when Migrate cannot create its shadow database.

For the record, these lines from the reproduction log show the versions the outputs above came from: the Node.js version, the SELECT version() string of the local server, and the installed client-side packages.

v26.7.0
PostgreSQL 17.5 on wasm32-unknown-linux-gnu, compiled by emcc (Emscripten gcc/clang-like replacement + linker emulating GNU ld) 3.1.74 (1092ec30a3fb1d46b1782ff1b4db5094d3d06ae5), 32-bit
@prisma/client@7.10.0
@prisma/adapter-pg@7.10.0
pg@8.23.1
tsx@4.23.15

The Prisma CLI printed its own version at the foot of each failed prisma validate run in the same log:

Prisma CLI Version : 7.10.0

The local server was started with npx prisma dev. The local Prisma Postgres documentation writes this command as npx prisma@7 dev, because prisma dev does something else in the Prisma ORM 8 command-line tool. Whatever the stack, the rule stays the same: P2003 on a delete is the database refusing to leave orphaned rows, and the fix is either to remove the children yourself or to let the constraint remove them, after a migration has actually put that rule in the database.

Further reading: Prisma $queryRaw Fails With Variables Inside Single Quotes. Also see Prisma Nested Create Returns Undefined: Include the Relation. Also see Prisma nested connect: syntax error at or near "ON", Keystone 6 Foreign Key NOT NULL on a Relationship Field.