Prisma One-To-Many关联异常：添加同商品到购物车触发唯一约束错误

问题场景 #

数据库中存在多个购物车（Cart），每个购物车包含多个Item：

• 首次添加某商品的Item到购物车时正常
• 再次添加同一商品的Item到购物车时，触发唯一约束错误

Prisma模型定义 #

model Product{
  Id Int @id @default(autoincrement())
  Uuid String @unique @default(uuid())
  Title String
  BrandName String?
  Thumbnails Thumbnail[]
  Price Float
  DiscountRate Float?
  Category Category @relation(fields: [CategoryId], references: [Id])
  CategoryId Int
  Status Status
  Favorite Boolean @default(false)
  Stock Int @default(0)
  Item Item[]
}

model Cart{
  Id Int @id @default(autoincrement())
  Items Item[]
}

model Item{
  Id Int @id @default(autoincrement())
  Product Product @relation(fields: [ProductId], references: [Id])
  ProductId Int  
  Quantity Int
  Cart Cart? @relation(fields: [CartId],references: [Id])
  CartId Int?
}

Next.js API代码（addItem.js） #

// addItem.js 

import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient();

export default async (req, res) => {
  
  let PrId = req.body.prodId;
  let Qte = req.body.Qte;
  let newItem = await prisma.item.create({
    data:{
      Quantity: Qte,
      Product:{
        connect:{
          Id: PrId,
        },
      }, 
      Cart:{
        connect:{
          Id: req.body.cartId
        }
      }
    },
  });

  return res.status(200).json(newItem);
};

错误信息 #

Unique constraint failed on the constraint: `Item_ProductId_key`
    at RequestHandler.handleRequestError (C:\Users\Bachar\Desktop\First_Freelance\node_modules\@prisma\client\runtime\index.js:30873:13)  
    at RequestHandler.request (C:\Users\Bachar\Desktop\First_Freelance\node_modules\@prisma\client\runtime\index.js:30856:12)
    at async PrismaClient._request (C:\Users\Bachar\Desktop\First_Freelance\node_modules\@prisma\client\runtime\index.js:31836:16)        
    at async __WEBPACK_DEFAULT_EXPORT__ (webpack-internal:///(api)/./pages/api/addItem.js:12:19)
    at async Object.apiResolver (C:\Users\Bachar\Desktop\First_Freelance\node_modules\next\dist\server\api-utils\node.js:367:9)
    at async DevServer.runApi (C:\Users\Bachar\Desktop\First_Freelance\node_modules\next\dist\server\next-server.js:474:9)
    at async Object.fn (C:\Users\Bachar\Desktop\First_Freelance\node_modules\next\dist\server\next-server.js:736:37)
    at async Router.execute (C:\Users\Bachar\Desktop\First_Freelance\node_modules\next\dist\server\router.js:252:36)
    at async DevServer.run (C:\Users\Bachar\Desktop\First_Freelance\node_modules\next\dist\server\base-server.js:381:29)
    at async DevServer.run (C:\Users\Bachar\Desktop\First_Freelance\node_modules\next\dist\server\dev\next-dev-server.js:724:20) {        
  code: 'P2002',
  clientVersion: '4.5.0',
  meta: { target: 'Item_ProductId_key' },
  page: '/api/addItem'
}

排查与解决方法 #

1. 修正数据库约束 #

错误提示Item_ProductId_key说明数据库中Item表的ProductId字段被设置了唯一约束，但业务上同一商品应该可以关联多个Item（比如不同购物车或同一购物车多次添加）：

• 直接操作数据库：找到Item表，删除ProductId字段对应的唯一约束
• 检查Prisma迁移历史：查看之前的迁移脚本，是否误给ProductId添加了@unique属性，修正模型后重新执行迁移命令（prisma migrate dev）

2. 优化业务逻辑（可选） #

从购物车常规逻辑出发，同一购物车中同一商品应该更新数量而非创建新Item，可修改API代码实现：

// 优化后的addItem.js
import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient();

export default async (req, res) => {
  const { prodId, Qte, cartId } = req.body;

  // 先查询购物车中是否已有该商品的Item
  const existingItem = await prisma.item.findFirst({
    where: {
      ProductId: prodId,
      CartId: cartId
    }
  });

  let result;
  if (existingItem) {
    // 存在则更新数量
    result = await prisma.item.update({
      where: { Id: existingItem.Id },
      data: { Quantity: existingItem.Quantity + Qte }
    });
  } else {
    // 不存在则创建新Item
    result = await prisma.item.create({
      data: {
        Quantity: Qte,
        Product: { connect: { Id: prodId } },
        Cart: { connect: { Id: cartId } }
      }
    });
  }

  return res.status(200).json(result);
};

内容的提问来源于stack exchange，提问作者Bachar ELkarni