Fix Prisma 'Unique constraint failed on the fields: (id)'

TL;DR#

@default(autoincrement()) on PostgreSQL is backed by a sequence, and the sequence only advances when the database generates an id itself. If rows were inserted with explicit ids, by a seed, an import or a copy from another database, the sequence still points at a value that already exists, and the next create() collides with the primary key. Move the sequence past the highest id with setval(pg_get_serial_sequence('"Artist"', 'id'), coalesce(max(id), 0) + 1, false), then stop passing ids in the code that loads data, or run the same reset after each load.

The Stack Overflow question shows a NestJS service calling this.db.artist.create() with a name, a slug and two Spotify fields, and no id at all. The schema declares id Int @id @default(autoincrement()), yet the insert fails with Unique constraint failed on the fields: (`id`). That wording comes from the 2022 report.

On Prisma 7.10.1 with the pg driver adapter, the same cause prints a different line. Reproduced with @prisma/adapter-pg 7.10.1 and PostgreSQL 18.6 (the official PostgreSQL 18 Docker image, 24 October 2026), with three rows imported with ids 1, 2 and 3 and the exact payload from the question (code frame trimmed):

BEFORE PrismaClientKnownRequestError P2002
Invalid `prisma.artist.create()` invocation in
/app/repro.mts:32:35
Unique constraint failed on the constraint: `Artist_pkey`
META {"driverAdapterError":{"name":"DriverAdapterError","cause":{"originalCode":"23505","originalMessage":"duplicate key value violates unique constraint \"Artist_pkey\"","kind":"UniqueConstraintViolation","constraint":{"index":"Artist_pkey"},"table":"Artist"}},"modelName":"Artist"}

The reproduction raised P2002, which the Prisma 7 error reference lists as a unique constraint failure; the 2022 wording fits the same message template, with the failing fields where the current client names the constraint. The PostgreSQL error underneath is 23505, and the constraint is Artist_pkey, the primary key on id. Whichever wording you see, the diagnosis and the fix below are the same.

Why it happens#

On PostgreSQL, autoincrement() is a column whose default calls a sequence. After prisma db push, the reproduction read it back from the catalog:

SQL> SELECT column_default FROM information_schema.columns WHERE table_name = 'Artist' AND column_name = 'id'
  [{"column_default":"nextval('\"Artist_id_seq\"'::regclass)"}]
SQL> SELECT pg_get_serial_sequence('"Artist"', 'id') AS seq
  [{"seq":"public.\"Artist_id_seq\""}]

The default only runs when the insert leaves id out. An insert that supplies id writes the row and never touches "Artist_id_seq". After three such rows, the table and the sequence disagree:

SQL> SELECT max(id) FROM "Artist"
  [{"max":3}]
SQL> SELECT last_value, is_called FROM "Artist_id_seq"
  [{"last_value":"1","is_called":false}]

is_called false means the next call to the sequence returns last_value itself, so the next generated id is 1. Row 1 exists, PostgreSQL raises a duplicate key on Artist_pkey, and Prisma turns it into P2002. Your create() payload is not the problem: the same payload on an empty table returned CONTROL1 OK id=1.

The rows with explicit ids can come from several places that look unrelated to the failing code:

• a seed script that writes fixed ids so that relations line up;
• a CSV import, or rows copied from another database with their ids (the case Answer 3 describes, pointing to the classic PostgreSQL thread on users_pkey);
• a createMany() in Prisma that includes id, which is how the reproduction loaded its three rows;
• rows typed into a database GUI with the id filled in.

Fix#

1. Confirm the sequence is behind the table#

Run both queries against the database that throws. Quote "Artist" exactly as Prisma created it, because the model name keeps its capital letter.

SELECT max(id) FROM "Artist";
SELECT last_value, is_called FROM "Artist_id_seq";

Work out the next id the sequence will hand out: it is last_value when is_called is false, and last_value + 1 when is_called is true. If that next id is not greater than max(id), this is your cause. A healthy table after normal inserts shows last_value equal to max(id) with is_called true, which is fine. For another table, read the sequence name from pg_get_serial_sequence rather than guessing it, since a mapped table names it differently. If the sequence is already ahead of every id, the collision is on another unique field: read meta on the error, which names the constraint (Artist_pkey here means the primary key).

2. Move the sequence past the highest id#

SELECT setval(
  pg_get_serial_sequence('"Artist"', 'id'),
  coalesce(max(id), 0) + 1,
  false
) FROM "Artist";

PostgreSQL's setval with a third argument of false makes the next value equal to the number you pass, so the next id is max(id) + 1. pg_get_serial_sequence finds the sequence owned by the column, so you do not have to guess its name. In the reproduction the call returned 4 and left the sequence at last_value 4 with is_called false.

Repeat the statement for every table that received rows with explicit ids, changing the table name in both places.

3. Run the reset from Prisma when the import lives in code#

When the explicit ids come from a seed or an import script, put the reset at the end of the same script so the database is never left in the broken state. This is the call the reproduction ran, written as a tagged template with no interpolated values:

import { PrismaClient } from '@prisma/client';
import { PrismaPg } from '@prisma/adapter-pg';
 
const prisma = new PrismaClient({ adapter: new PrismaPg({ connectionString: process.env.DATABASE_URL }) });
 
await prisma.artist.createMany({ data: [1, 2, 3].map((id) => ({ id, name: 'imported-' + id })) });
await prisma.$queryRaw`SELECT setval(pg_get_serial_sequence('"Artist"', 'id'), coalesce(max(id), 0) + 1, false) FROM "Artist"`;
await prisma.$disconnect();

It uses $queryRaw because the statement is a SELECT that returns the new value.

4. Stop writing ids the database should generate#

The durable fix is to leave id out of create() and createMany() calls when the column is autoincrement(), as the highest-voted answer on the question recommends. If fixed ids are genuinely needed, for fixtures that other rows reference, keep step 3 next to them.

Verify the fix#

Run the same create() that failed. After the reset, the reproduction printed:

setval returned [{"setval":"4"}]
SQL> SELECT last_value, is_called FROM "Artist_id_seq"
  [{"last_value":"4","is_called":false}]
AFTER OK id=4
AFTER-2 OK id=5

The first new row takes id 4, the one after it takes 5, and the sequence now moves with the table. Run the step 1 queries again after the next import: if the next id the sequence would hand out is no longer greater than max(id), something in that import still writes ids.

Retries, id 0 and an empty table: what the reproduction ruled out#

Retrying is not a fix. With rows 1 to 3 imported and no reset, calling the same create() four times gave:

attempt 1 P2002
attempt 2 P2002
attempt 3 P2002
attempt 4 OK id=4

Each failed insert still consumed a sequence value. The PostgreSQL sequence documentation explains that a value obtained from a sequence is not handed back when the transaction that used it aborts. After three failures the sequence had stepped past the imported rows, so the error appeared to cure itself. A larger import would need as many failed requests as there are imported rows, and the next import breaks it again.

A row with id 0 is not the cause. One answer on the question suggests rows added with id 0. With a single row 0 present and nothing else, create() returned CONTROL2 OK id=1: the sequence starts at 1 and never produces 0.

The short form fails on an empty table. The common one-liner setval(..., (SELECT max(id) FROM "Artist")) does nothing when the table is empty, because max(id) is null:

SQL> SELECT setval(pg_get_serial_sequence('"Artist"', 'id'), (SELECT max(id) FROM "Artist"))
  [{"setval":null}]
SQL> SELECT setval(pg_get_serial_sequence('"Artist"', 'id'), 0)
  ERROR 22003: setval: value 0 is out of bounds for sequence "Artist_id_seq" (1..2147483647)

Replacing null with 0 is worse, since 0 is below the sequence's minimum of 1. The coalesce(max(id), 0) + 1 form with false returned 1 on the empty table, which is what a fresh table needs.

If the reset now fails with 42P01#

The table name passed to pg_get_serial_sequence is parsed like SQL, so an unquoted name is folded to lowercase. Without the inner double quotes, the reproduction got:

SQL> SELECT pg_get_serial_sequence('Artist', 'id')
  ERROR 42P01: relation "artist" does not exist

Keep both sets of quotes, '"Artist"', for any model whose table name has a capital letter. The same folding rule is behind Prisma raw queries that cannot find a table that exists, which covers how to match the physical name in raw SQL. A different Prisma constraint code, P2003, is raised when a delete is blocked by child rows that still reference the parent; it is covered in Prisma deleteMany: Foreign key constraint failed. A create() that fails without any database error is a different problem again: Could not figure out an ID in create appears when the INSERT ... RETURNING comes back with no row at all.

The useful habit is to treat an id sequence as state that has to be kept in step with the table: every path that writes ids by hand, whether a seed, a restore or a copy between environments, should end by moving the sequence past max(id).