How to fix P2004: A constraint failed on the database in Prisma

PrismaClientKnownRequestError:
P2004: A constraint failed on the database:
Unique constraint failed on the fields: (`email`)

PrismaClientKnownRequestError:
P2004: A constraint failed on the database:
Foreign key constraint failed on the field: `userId`

// schema.prisma
model User {
  id    String  @id @default(cuid())
  email String  @unique  // This creates a unique constraint
  name  String?
}

// schema.prisma
model User {
  id    String  @id @default(cuid())
  email String  @unique
  posts Post[]
}

model Post {
  id       String  @id @default(cuid())
  title    String
  content  String?
  userId   String
  user     User    @relation(fields: [userId], references: [id])
}

// This will fail if user with ID "non-existent-id" doesn't exist
await prisma.post.create({
  data: {
    title: "My Post",
    content: "Post content",
    userId: "non-existent-id"  // Foreign key violation!
  }
});

// Solutions:
// 1. Ensure the referenced record exists first
const user = await prisma.user.findUnique({
  where: { id: "some-user-id" }
});

if (!user) {
  // Create the user first, or use a valid user ID
  const newUser = await prisma.user.create({
    data: { email: "user@example.com" }
  });

  await prisma.post.create({
    data: {
      title: "My Post",
      content: "Post content",
      userId: newUser.id  // Valid foreign key
    }
  });
} else {
  await prisma.post.create({
    data: {
      title: "My Post",
      content: "Post content",
      userId: user.id  // Valid foreign key
    }
  });
}

// 2. Use nested writes (Prisma handles foreign keys automatically)
await prisma.user.create({
  data: {
    email: "newuser@example.com",
    posts: {
      create: {
        title: "My Post",
        content: "Post content"
      }
    }
  }
});

// schema.prisma with database-level constraints
model Product {
  id          String   @id @default(cuid())
  name        String
  price       Decimal  @db.Decimal(10, 2)
  category    String

  // Database-level check constraint (PostgreSQL example)
  @@map("products")
  // In migrations: ADD CONSTRAINT price_positive CHECK (price > 0)
}

// This might fail due to database check constraints
await prisma.product.create({
  data: {
    name: "Test Product",
    price: -10.00,  // Might violate "price > 0" check constraint
    category: "Electronics"
  }
});

// Solutions:
// 1. Check your database migration files for constraints
// 2. Validate data before sending to database
if (price <= 0) {
  throw new Error("Price must be positive");
}

// 3. For NOT NULL constraints without defaults:
// schema.prisma: name String  // NOT NULL by default
await prisma.product.create({
  data: {
    name: null,  // This will cause P2004 in database
    price: 10.00,
    category: "Electronics"
  }
});

// Fix: Always provide required fields or set defaults in schema
model Product {
  id       String   @id @default(cuid())
  name     String   // Required - must provide value
  price    Decimal  @db.Decimal(10, 2)
  category String   @default("Uncategorized")  // Default value
}

// 1. Use transactions for atomic operations
await prisma.$transaction(async (tx) => {
  // Check constraints within transaction
  const existing = await tx.user.findUnique({
    where: { email: "test@example.com" }
  });

  if (existing) {
    throw new Error("User already exists");
  }

  // Create within same transaction
  await tx.user.create({
    data: {
      email: "test@example.com",
      name: "Test User"
    }
  });
});

// 2. Add application-level validation
import { z } from 'zod';

const userSchema = z.object({
  email: z.string().email(),
  name: z.string().min(1)
});

const validatedData = userSchema.parse(userInput);

// 3. Use database-specific error handling
try {
  await prisma.user.create({ data: userData });
} catch (error) {
  if (error.code === 'P2004') {
    console.error('Constraint violation:', error.meta);

    if (error.meta?.constraint === 'User_email_key') {
      // Handle duplicate email specifically
      return { error: 'Email already exists' };
    }

    if (error.meta?.constraint?.includes('foreign')) {
      // Handle foreign key violation
      return { error: 'Referenced record does not exist' };
    }
  }
  throw error;
}

// 4. Review and understand your database schema
// Run: npx prisma db pull
// Examine the generated Prisma schema and actual database constraints