CODEWITHSUNDEEP
prismaprisma-graphqlprisma2
TemuujinNat
TemuujinNat

Reputation: 185

Prisma - How to define compound unique constraint with fields in multiple models?

I have this not so straightforward model relationship in Prisma. User --< Enrollment >-- Course and I can't figure out how to ensure the Course title field is unique just among that user's created courses. In other words, I dont want one user to create multiple courses with the same name. But I want courses to exist with the same name with different Creators. (Only the creator has the TEACHER role in the Enrollment)

The problem I'm facing is, I don't know where to define a unique attribute and what fields to include. The fields I want to make a unique constraint on (Course name, Member who has TEACHER role) are across different models.

model User {
  id                Int              @id @default(autoincrement())
  email             String           @unique
  passwordHash      String
  enrollments       Enrollment[]
}

model Course {
  id                Int              @id @default(autoincrement())
  name              String
  members           Enrollment[]
}

model Enrollment {
  role              UserRole         @default(STUDENT)

  // Relation Fields
  userId            Int
  courseId         Int
  user              User             @relation(fields: [userId], references: [id])
  course           Course          @relation(fields: [courseId], references: [id])
  @@id([userId, courseId])
  @@index([userId, role])
}

Upvotes: 15

Views: 20444

Answers (1)

Xetera
Xetera

Reputation: 1479

You probably want to add a field on your Course model that defines who created it so you can use the field as a unique constraint on the table itself.

model Course {
  id                Int              @id @default(autoincrement())
  name              String
  members           Enrollment[]
  creatorId         Int
  creator           User             @relation(fields: [creatorId], references: [id])
  @@unique([creatorId, name], name: "courseIdentifier")
}

Upvotes: 36

Related Questions