Prisma含可选字段的复合唯一约束查询报错问题

问题核心 #

你的模型中courseId和packageId二选一非空，且定义了包含这两个字段的复合唯一约束planIdentifier。但Prisma客户端对复合唯一约束的输入类型有严格校验——要求约束内的所有字段必须提供值（即使是null），且部分情况下不允许传入null，导致Upsert时触发缺失字段的报错。同时直接拆分约束会因Prisma不将null视为唯一值引发新的冲突。

以下是三种可行的解决方案：

方案1：调整模型结构（推荐） #

新增枚举区分目标类型，用统一的非空字段替代courseId和packageId，彻底规避null问题：

1. 先定义枚举类型：

enum TargetType {
  COURSE
  PACKAGE
}

2. 修改UserPlanCourse模型：

model UserPlanCourse {
  id         Int         @id @default(autoincrement())
  planId     Int?
  userPlan   UserPlan?   @relation(fields: [planId], references: [id])
  targetId   Int         // 统一存储课程/包的ID
  targetType TargetType  // 标记当前记录是课程还是包
  year       Int
  semesterId Int
  semester   Semester    @relation(fields: [semesterId], references: [id])

  // 条件关联（Prisma 4.16+支持），确保关联匹配对应类型
  course     Course?     @relation(fields: [targetId], references: [id], where: {targetType: COURSE})
  package    Package?    @relation(fields: [targetId], references: [id], where: {targetType: PACKAGE})

  @@unique([planId, targetId, targetType], name: "planIdentifier")
  @@index([targetId])
  @@index([semesterId])
  @@index([planId])
}

3. 修改Upsert代码：

const targetType = course.title ? 'COURSE' : 'PACKAGE';
upsertCalls.push(prisma.userPlanCourse.upsert({
  where: {
    planIdentifier: {
      planId: data.planId,
      targetId: course.id,
      targetType
    }
  },
  update: { year: data.year, semesterId: data.semesterId },
  create: {
    ...data,
    targetId: course.id,
    targetType
  }
}));

该方案让业务逻辑更清晰，完全避免null带来的约束问题，同时保留Prisma的类型安全特性。

方案2：使用部分唯一索引 #

利用数据库的部分唯一索引特性，分别为[planId, courseId]和[planId, packageId]创建仅当对应字段非空时生效的唯一约束：

1. 修改模型的约束配置：

model UserPlanCourse {
  id         Int       @id @default(autoincrement())
  planId     Int?
  userPlan   UserPlan? @relation(fields: [planId], references: [id])
  courseId   Int?
  course     Course?   @relation(fields: [courseId], references: [id])
  packageId  Int?
  package    Package?  @relation(fields: [packageId], references: [id])
  year       Int
  semesterId Int
  semester   Semester  @relation(fields: [semesterId], references: [id])

  // 仅当courseId非空时，约束planId+courseId唯一
  @@unique([planId, courseId], name: "plan_course_unique", where: courseId is not null)
  // 仅当packageId非空时，约束planId+packageId唯一
  @@unique([planId, packageId], name: "plan_package_unique", where: packageId is not null)

  @@index([packageId])
  @@index([courseId])
  @@index([semesterId])
  @@index([planId])
}

2. 修改Upsert代码，根据类型选择对应唯一索引：

if (course.title) {
  data.courseId = course.id;
  upsertCalls.push(prisma.userPlanCourse.upsert({
    where: { plan_course_unique: { planId: data.planId, courseId: data.courseId } },
    update: { year: data.year, semesterId: data.semesterId },
    create: data
  }));
} else {
  data.packageId = course.id;
  upsertCalls.push(prisma.userPlanCourse.upsert({
    where: { plan_package_unique: { planId: data.planId, packageId: data.packageId } },
    update: { year: data.year, semesterId: data.semesterId },
    create: data
  }));
}

此方案无需大幅调整业务逻辑，部分唯一索引已被PostgreSQL、MySQL 8.0+等主流数据库支持。

方案3：原生SQL Upsert（绕过Prisma客户端校验） #

如果不想修改模型，可直接使用Prisma的原生SQL执行Upsert，绕过客户端的字段校验：

if (course.title) {
  data.courseId = course.id;
  const sql = `
    INSERT INTO "UserPlanCourse" ("planId", "courseId", "year", "semesterId")
    VALUES ($1, $2, $3, $4)
    ON CONFLICT ("planId", "courseId", "packageId") WHERE "courseId" IS NOT NULL
    DO UPDATE SET "year" = $3, "semesterId" = $4;
  `;
  upsertCalls.push(prisma.$executeRawUnsafe(sql, data.planId, data.courseId, data.year, data.semesterId));
} else {
  data.packageId = course.id;
  const sql = `
    INSERT INTO "UserPlanCourse" ("planId", "packageId", "year", "semesterId")
    VALUES ($1, $2, $3, $4)
    ON CONFLICT ("planId", "courseId", "packageId") WHERE "packageId" IS NOT NULL
    DO UPDATE SET "year" = $3, "semesterId" = $4;
  `;
  upsertCalls.push(prisma.$executeRawUnsafe(sql, data.planId, data.packageId, data.year, data.semesterId));
}

该方案适合快速临时解决问题，但会失去Prisma的类型安全优势。

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