Hi, I have a 3 way mapping table where one of the foreign keys are nullable. Is it possible to create a

UNIQUE INDEX

with a where clause? Model:

model UserRoles {
  id          Int      @id @default(autoincrement())
  user_id     Int
  role_id     Int
  facility_id Int?

  // Mapping relations
  user     User      @relation("UserRelation", fields: [user_id], references: [id])
  role     Role      @relation("RoleRelation", fields: [role_id], references: [id])
  facility Facility? @relation("FacilityRelation", fields: [facility_id], references: [id])
}

I want to add the following 2 indexes:

CREATE UNIQUE INDEX key_a ON user_roles (user_id, role_id)
  WHERE facility_id IS NULL;

CREATE UNIQUE INDEX key_b ON user_roles (user_id, role_id, facility_id)
  WHERE facility_id IS NOT NULL;

If Prisma does not allow me to do this, then what would be be the correct implementation for this? Thank you!

Not entirely possible, however, you might want to read up on null busters as a workaround to achieve this: https://medium.com/nerd-for-tech/the-nullbuster-workaround-for-the-unique-index-in-sql-9423c315cd2e

@Mike Willbanks Thanks you vm! I'm working on implementing this idea.