How to fix P2002: Unique constraint failed on the fields in Prisma

PrismaINTERMEDIATEMEDIUMUpdated Mar 5, 2026· 307 views

The Prisma P2002 error occurs when you attempt to insert or update a record with a value that violates a unique constraint in your database. This happens when trying to create a duplicate entry for a field marked as @unique, such as an email address that already exists. The fix typically involves checking for existing records before insertion or using upsert operations.

Quick fix

Fixes P2002: Unique constraint failed on the fields

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

try {
  await prisma.user.create({
    data: {
      email: 'alice@example.com',
      name: 'Alice'
    }
  });
} catch (error) {
  if (error instanceof Prisma.PrismaClientKnownRequestError) {
    if (error.code === 'P2002') {
      console.log('Unique constraint failed on:', error.meta?.target);
      // Output: Unique constraint failed on: ['email']
    }
  }
}
  1. Identify which field caused the unique constraint violation
  2. Check for existing records before creating
  3. Use upsert for insert-or-update operations
  4. Handle P2002 errors gracefully in your application
prisma — error
import { Prisma } from '@prisma/client';
try {
  await prisma.user.create({
    data: {
      email: 'alice@example.com',
      name: 'Alice'
    }
  });
} catch (error) {
  if (error instanceof Prisma.PrismaClientKnownRequestError) {
    if (error.code === 'P2002') {
      console.log('Unique constraint failed on:', error.meta?.target);
      // Output: Unique constraint failed on: ['email']
    }
  }
}
P2002: Unique constraint failed on the fields

What this error means

The P2002 error in Prisma indicates a unique constraint violation at the database level. This error is thrown by Prisma Client as a PrismaClientKnownRequestError when you try to create or update a record with a value that must be unique but already exists in the database. Unique constraints are database-level rules that ensure data integrity by preventing duplicate values in specific fields or combinations of fields. For example, if your User model has an email field marked with @unique, attempting to create two users with the same email address will trigger the P2002 error. This error is part of the Prisma error code system (P2000-P2099 range for query engine errors) and includes metadata about which field or fields caused the violation. The error helps maintain data consistency and is a fundamental aspect of relational database integrity. The error message typically includes the constraint name and the target fields, making it easier to identify exactly which unique constraint was violated and where the duplicate value exists.

Typical symptoms

  • Prisma Client throws PrismaClientKnownRequestError with code P2002
  • Database operations fail with "Unique constraint failed" message
  • Error occurs during create or update operations with duplicate values
  • Error metadata includes the specific field names that caused the violation
  • Concurrent operations may intermittently fail with this error
  • Upsert operations may throw P2002 during race conditions

Common causes

  1. Attempting to create a record with a duplicate value in a @unique field
  2. Updating a record to a value that already exists in another record
  3. Race conditions where multiple concurrent operations try to create the same unique value
  4. Not checking for existing records before insertion
  5. Using create instead of upsert when records might already exist
  6. Compound unique constraints violated by a combination of field values
  7. Data import or seeding with duplicate unique field values

How to fix "P2002: Unique constraint failed on the fields"

1Identify which field caused the unique constraint violation

Examine the error object to identify which field or fields violated the unique constraint:

typescript
import { Prisma } from '@prisma/client';

try {
  await prisma.user.create({
    data: {
      email: 'alice@example.com',
      name: 'Alice'
    }
  });
} catch (error) {
  if (error instanceof Prisma.PrismaClientKnownRequestError) {
    if (error.code === 'P2002') {
      console.log('Unique constraint failed on:', error.meta?.target);
      // Output: Unique constraint failed on: ['email']
    }
  }
}

The error.meta.target property contains an array of field names that caused the violation. This helps you understand exactly which unique constraint was broken.

Note: Some database adapters (like PlanetScale/Vitess or Neon in certain configurations) may not provide the field name in the error metadata.

2Check for existing records before creating

Prevent P2002 errors by checking if a record with the same unique value already exists:

typescript
async function createUserSafely(email: string, name: string) {
  // Check if user with this email already exists
  const existingUser = await prisma.user.findUnique({
    where: { email }
  });

  if (existingUser) {
    throw new Error(`User with email ${email} already exists`);
    // Or return the existing user
    // return existingUser;
  }

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

This approach prevents the error but requires an additional database query. For better performance, consider using upsert (see next step).

3Use upsert for insert-or-update operations

The upsert operation atomically creates a record if it doesn't exist or updates it if it does:

typescript
const user = await prisma.user.upsert({
  where: { email: 'alice@example.com' },
  update: {
    name: 'Alice Updated'
  },
  create: {
    email: 'alice@example.com',
    name: 'Alice'
  }
});

This is ideal when:
- You want to create a record if it doesn't exist
- You want to update it if it does exist
- You want to avoid the P2002 error entirely

For compound unique constraints:

typescript
// Schema: @@unique([userId, postId])
const like = await prisma.like.upsert({
  where: {
    userId_postId: {
      userId: 1,
      postId: 100
    }
  },
  update: {
    createdAt: new Date()
  },
  create: {
    userId: 1,
    postId: 100
  }
});

Note: Under high concurrency, upsert may still throw P2002 errors due to race conditions. Handle these with retry logic (see step 5).

4Handle P2002 errors gracefully in your application

Implement proper error handling to provide meaningful feedback:

typescript
import { Prisma } from '@prisma/client';

async function createUser(email: string, name: string) {
  try {
    return await prisma.user.create({
      data: { email, name }
    });
  } catch (error) {
    if (error instanceof Prisma.PrismaClientKnownRequestError) {
      if (error.code === 'P2002') {
        const target = error.meta?.target as string[] | undefined;
        const field = target?.join(', ') || 'unknown field';

        throw new Error(
          `A user with this ${field} already exists. Please use a different value.`
        );
      }
    }
    throw error;
  }
}

For API responses:

typescript
app.post('/users', async (req, res) => {
  try {
    const user = await createUser(req.body.email, req.body.name);
    res.status(201).json(user);
  } catch (error) {
    if (error instanceof Prisma.PrismaClientKnownRequestError && error.code === 'P2002') {
      return res.status(409).json({
        error: 'Conflict',
        message: 'A user with this email already exists'
      });
    }
    res.status(500).json({ error: 'Internal server error' });
  }
});
5Implement retry logic for race conditions

Race conditions can occur when multiple operations attempt to create the same unique value simultaneously. Implement retry logic:

typescript
async function upsertWithRetry<T>(
  operation: () => Promise<T>,
  maxRetries: number = 3,
  delay: number = 100
): Promise<T> {
  let lastError: any;

  for (let attempt = 0; attempt < maxRetries; attempt++) {
    try {
      return await operation();
    } catch (error) {
      if (error instanceof Prisma.PrismaClientKnownRequestError &&
          error.code === 'P2002') {
        lastError = error;
        // Wait before retrying with exponential backoff
        await new Promise(resolve =>
          setTimeout(resolve, delay * Math.pow(2, attempt))
        );
        continue;
      }
      throw error;
    }
  }

  throw lastError;
}

// Usage
const user = await upsertWithRetry(() =>
  prisma.user.upsert({
    where: { email: 'alice@example.com' },
    update: { name: 'Alice' },
    create: { email: 'alice@example.com', name: 'Alice' }
  })
);

This is especially important for:
- High-traffic applications
- Concurrent user registrations
- Distributed systems
- Background job processing

6Review your Prisma schema unique constraints

Verify that your unique constraints are correctly defined in your Prisma schema:

prisma
model User {
  id       String   @id @default(cuid())
  email    String   @unique  // Single field unique constraint
  username String   @unique
  name     String
}

model Post {
  id        String   @id @default(cuid())
  title     String
  slug      String
  authorId  String

  // Compound unique constraint - combination must be unique
  @@unique([slug, authorId])
}

model UserProfile {
  userId    String   @unique  // One-to-one relation
  bio       String?
  user      User     @relation(fields: [userId], references: [id])
}

Key considerations:
- Use @unique for single field constraints
- Use @@unique([field1, field2]) for compound constraints
- Ensure unique constraints match your business logic
- Consider case-sensitivity: some databases treat 'alice@example.com' and 'Alice@example.com' as different

After modifying unique constraints, create a migration:

bash
npx prisma migrate dev --name add-unique-constraint

Advanced notes

Database-Specific Behavior: Different databases handle unique constraints differently:
- PostgreSQL: Case-sensitive unique constraints by default. Use LOWER(email) in a unique index for case-insensitive uniqueness
- MySQL: Case-insensitive for VARCHAR by default (depends on collation)
- SQLite: Case-sensitive unique constraints
- SQL Server: NULL values are considered distinct in unique constraints (multiple NULLs allowed)

Performance Optimization:
- Unique constraints automatically create indexes, improving query performance
- Use findUnique instead of findFirst when querying unique fields (more efficient)
- For existence checks, use select: { id: true } to minimize data transfer
- Consider database-level unique indexes for fields that aren't in your Prisma schema

Compound Unique Constraints: When using @@unique([field1, field2]), the where clause must include all fields:

typescript
// Correct
await prisma.like.upsert({
  where: {
    userId_postId: { userId: 1, postId: 100 }
  },
  // ...
});

// Incorrect - will not work
await prisma.like.upsert({
  where: { userId: 1 }, // Missing postId
  // ...
});

Testing Strategies: Write tests to ensure unique constraint violations are handled:

typescript
describe('User creation', () => {
  it('should prevent duplicate emails', async () => {
    await prisma.user.create({
      data: { email: 'test@example.com', name: 'Test' }
    });

    await expect(
      prisma.user.create({
        data: { email: 'test@example.com', name: 'Test2' }
      })
    ).rejects.toThrow(/P2002/);
  });
});

Partial Unique Indexes: Some databases support conditional unique constraints (not directly in Prisma schema but via raw SQL):

sql
-- PostgreSQL: Unique only for non-deleted users
CREATE UNIQUE INDEX unique_active_emails
ON "User" (email)
WHERE "deletedAt" IS NULL;

Migration Considerations: When adding unique constraints to existing data:
1. Check for existing duplicates first
2. Clean up duplicate data before adding the constraint
3. Consider using a multi-step migration to handle large datasets

Prisma Version Features:
- Prisma 2.20+: Improved error messages with field information
- Prisma 4.0+: Better error metadata and debugging capabilities
- Always check error.meta.target for the specific fields that caused the violation

Frequently asked questions

What does "P2002: Unique constraint failed on the fields" mean?
The P2002 error in Prisma indicates a unique constraint violation at the database level. This error is thrown by Prisma Client as a PrismaClientKnownRequestError when you try to create or update a record with a value that must be unique but already exists in the database. Unique constraints are database-level rules that ensure data integrity by preventing duplicate values in specific fields or combinations of fields.
How do I fix "P2002: Unique constraint failed on the fields"?
The Prisma P2002 error occurs when you attempt to insert or update a record with a value that violates a unique constraint in your database. This happens when trying to create a duplicate entry for a field marked as @unique, such as an email address that already exists. The fix typically involves checking for existing records before insertion or using upsert operations.
What causes "P2002: Unique constraint failed on the fields"?
Attempting to create a record with a duplicate value in a @unique field Updating a record to a value that already exists in another record Race conditions where multiple concurrent operations try to create the same unique value
Is the "P2002: Unique constraint failed on the fields" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading