Prisma where condition on compound unique constraint with optional fields

564 Views Asked by At

I have this model:

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])

  @@unique([planId, courseId, packageId], name: "planIdentifier")
  @@index([packageId])
  @@index([courseId])
  @@index([semesterId])
  @@index([planId])
}

I've marked course and package both as optional because at a time, only one of them will have a value (not sure if there's a better way to do this but that's not the problem for now).

The idea with the unique constraint is that any combination of planId and either courseId or packageId will always be unique. Inserting data to this works fine. But when I try to use a where condition on planIdentifier it fails with the error

Missing a required value at Mutation.upsertOneUserPlanCourse.where.UserPlanCourseWhereUniqueInput.planIdentifier.UserPlanCoursePlanIdentifierCompoundUniqueInput.packageId

Here's the code that uses the where condition:

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

Note: I've tried having separate unique constraints for [planId, courseId] and [planId, packageId] but this doesn't work since entries with the same planId and different courseId would have the same null value in the packageId field which violates one of the constraints. Prisma doesn't support considering null values as unique afaik.

0

There are 0 best solutions below