Fix: Prisma Unique Constraint Failed on the Fields

The Error

A Prisma create or update operation throws a unique constraint error:

PrismaClientKnownRequestError:
Unique constraint failed on the fields: (`email`)
  code: 'P2002',
  meta: { target: ['email'] }

Or with a composite unique constraint:

PrismaClientKnownRequestError:
Unique constraint failed on the fields: (`userId`, `postId`)
  code: 'P2002',
  meta: { target: ['userId', 'postId'] }

The operation attempted to insert or update a record where the unique field value already exists in the database.

Why This Happens

Prisma propagates the database’s unique constraint violation as error code P2002. This occurs when:

• Duplicate record creation — you call prisma.user.create() with an email that’s already in the database.
• No existence check before insert — the code doesn’t verify whether a record exists before trying to create it.
• Race condition — two concurrent requests both check “does this record exist?” get “no”, then both try to create it. One succeeds, one fails.
• Seed data run twice — a database seeder that doesn’t check for existing records fails on the second run.
• Composite unique constraint — a combination of fields (e.g., userId + postId for a join table) must be unique, and a duplicate combination was inserted.
• Case sensitivity — [email protected] and [email protected] might be treated as the same by your app but stored as different strings, causing unexpected duplicates when constraints are case-insensitive.

Fix 1: Catch P2002 and Return a Meaningful Error

At minimum, catch the Prisma error and return a user-friendly response instead of a 500:

import { PrismaClient, Prisma } from '@prisma/client';

const prisma = new PrismaClient();

async function createUser(data: { email: string; name: string }) {
  try {
    return await prisma.user.create({ data });
  } catch (e) {
    if (e instanceof Prisma.PrismaClientKnownRequestError) {
      if (e.code === 'P2002') {
        const field = (e.meta?.target as string[])?.join(', ') ?? 'field';
        throw new Error(`A user with this ${field} already exists.`);
      }
    }
    throw e;
  }
}

In an Express route handler:

app.post('/users', async (req, res) => {
  try {
    const user = await createUser(req.body);
    res.status(201).json(user);
  } catch (e) {
    if (e instanceof Error && e.message.includes('already exists')) {
      res.status(409).json({ error: e.message });  // 409 Conflict
    } else {
      res.status(500).json({ error: 'Internal server error' });
    }
  }
});

Why 409 not 400? HTTP 409 Conflict is the correct status code for a uniqueness violation — the request was valid, but it conflicts with the current state of the resource. 400 Bad Request implies the input was malformed.

Fix 2: Use upsert Instead of create

If the intent is “create if not exists, update if exists”, use upsert — it handles duplicates atomically:

const user = await prisma.user.upsert({
  where: { email: data.email },
  update: {
    name: data.name,           // Fields to update if record exists
    updatedAt: new Date(),
  },
  create: {
    email: data.email,         // Fields to set when creating
    name: data.name,
  },
});

For join tables (many-to-many), upsert handles the composite key:

const favorite = await prisma.userPostFavorite.upsert({
  where: {
    userId_postId: {           // Prisma generates this compound key
      userId: req.user.id,
      postId: req.params.postId,
    },
  },
  update: {},                  // Nothing to update — just ensure it exists
  create: {
    userId: req.user.id,
    postId: req.params.postId,
  },
});

upsert is not always appropriate. If creating a duplicate should be an error (e.g., a user trying to register with an existing email), catch the P2002 and return a 409 instead of silently updating the existing record.

Fix 3: Check Before Creating (with Caveats)

For cases where you want to explicitly verify uniqueness before inserting:

async function registerUser(email: string, name: string) {
  const existing = await prisma.user.findUnique({
    where: { email },
  });

  if (existing) {
    throw new Error('Email already registered');
  }

  return await prisma.user.create({
    data: { email, name },
  });
}

Caveat: This check-then-insert pattern has a race condition. Two concurrent requests can both pass the findUnique check and then both attempt create. In low-traffic scenarios this is acceptable, but for high-concurrency code (login endpoints, payment processing), use a database transaction or rely on catching P2002:

// Safer: attempt create, catch the uniqueness error
async function registerUser(email: string, name: string) {
  try {
    return await prisma.user.create({ data: { email, name } });
  } catch (e) {
    if (e instanceof Prisma.PrismaClientKnownRequestError && e.code === 'P2002') {
      throw new Error('Email already registered');
    }
    throw e;
  }
}

This approach is both correct under concurrency and doesn’t require an extra database round-trip.

Fix 4: Fix Seeder Scripts

Seed scripts that run create on every execution fail the second time. Use upsert or createMany with skipDuplicates:

// prisma/seed.ts

// Option A: upsert (works for updating existing seed data too)
await prisma.role.upsert({
  where: { name: 'admin' },
  update: {},
  create: { name: 'admin', permissions: ['read', 'write', 'delete'] },
});

// Option B: createMany with skipDuplicates (faster for large seeds)
await prisma.tag.createMany({
  data: [
    { name: 'javascript' },
    { name: 'typescript' },
    { name: 'python' },
  ],
  skipDuplicates: true,   // Silently skips records that violate unique constraints
});

skipDuplicates: true is particularly useful for reference data (countries, currencies, permissions) that should be idempotent — safe to run multiple times.

Fix 5: Handle Bulk Imports

When importing data from a CSV or external source, duplicates in the source data or against existing records cause failures:

async function importUsers(users: { email: string; name: string }[]) {
  const results = { created: 0, skipped: 0, errors: [] as string[] };

  for (const user of users) {
    try {
      await prisma.user.create({ data: user });
      results.created++;
    } catch (e) {
      if (e instanceof Prisma.PrismaClientKnownRequestError && e.code === 'P2002') {
        results.skipped++;  // Already exists — skip
      } else {
        results.errors.push(`${user.email}: ${(e as Error).message}`);
      }
    }
  }

  return results;
}

For large imports, use createMany with skipDuplicates for better performance:

async function bulkImportUsers(users: { email: string; name: string }[]) {
  const result = await prisma.user.createMany({
    data: users,
    skipDuplicates: true,
  });
  return { created: result.count, total: users.length };
}

Note: createMany with skipDuplicates uses a single SQL statement, which is dramatically faster than individual inserts in a loop for large datasets.

Fix 6: Handle Unique Constraints on Optional Fields

Unique constraints on nullable fields behave differently than you might expect. In most databases (PostgreSQL, MySQL), NULL values are not considered equal for uniqueness purposes — you can have multiple rows with NULL in a unique column.

Prisma schema example:

model User {
  id       Int     @id @default(autoincrement())
  email    String  @unique
  username String? @unique   // Nullable unique field
}

If your business logic requires that non-null values be unique but allows multiple nulls, this is correct. But if you expect null to be treated as unique (only one null allowed), you need a partial unique index — which must be added via a raw migration, not the Prisma schema DSL:

-- In a Prisma migration SQL file
CREATE UNIQUE INDEX users_username_unique
ON "User" (username)
WHERE username IS NOT NULL;

Fix 7: Debug Which Constraint Is Failing

The meta.target field in the error tells you exactly which field (or fields) caused the violation:

try {
  await prisma.user.create({ data });
} catch (e) {
  if (e instanceof Prisma.PrismaClientKnownRequestError && e.code === 'P2002') {
    console.log('Failing fields:', e.meta?.target);
    // e.g., ['email'] or ['userId', 'postId']
  }
}

Cross-reference with your Prisma schema:

model User {
  id    Int    @id @default(autoincrement())
  email String @unique              // Single field constraint

  @@unique([firstName, lastName])   // Composite constraint
}

If target shows ['firstName', 'lastName'], the combination of first + last name already exists.

Still Not Working?

Check whether the unique index exists in the database. If you added @unique to the schema but didn’t run prisma migrate dev, the database doesn’t have the constraint:

npx prisma migrate dev      # Apply pending migrations
npx prisma db push          # Or push schema directly (dev only)

Check if data was manually inserted into the database that violates constraints added later. If you added a unique constraint to a column that already has duplicates, the migration will fail:

# In psql — find duplicate emails before adding the constraint
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

Deduplicate the data before running the migration.

Verify @@unique placement. A common mistake is placing @@unique inside a field definition instead of at the model level:

model Post {
  id       Int    @id @default(autoincrement())
  userId   Int
  categoryId Int

  // Wrong — @unique here makes categoryId alone unique
  // categoryId Int @unique

  // Correct — composite unique at model level
  @@unique([userId, categoryId])
}

For related Prisma issues, see Fix: Prisma Migration Failed and Fix: Postgres Relation Does Not Exist.