P
Prisma•2mo ago
Zeke

Can't Null a Compound-unique Value

I have something like this: 
model MyModel {
  fieldOne String
  fieldTwo String
  fieldThree String?

  @@unique([fieldOne, fieldTwo, fieldThree])
}
model MyModel {
  fieldOne String
  fieldTwo String
  fieldThree String?

  @@unique([fieldOne, fieldTwo, fieldThree])
}
-# I wanted to make that a compound ID, but it wouldn't let me :/ I'm attempting to do a query like this: 
prisma.MyModel.upsert({
  where: {
    fieldOne_fieldTwo_fieldThree: {
       fieldOne: 'one',
       fieldTwo: 'two',
       fieldThree: null, // this gives me a typeerror, claiming it must be a string. fieldThree is nullable, so it *should* be allowed!
    }
  }
})
prisma.MyModel.upsert({
  where: {
    fieldOne_fieldTwo_fieldThree: {
       fieldOne: 'one',
       fieldTwo: 'two',
       fieldThree: null, // this gives me a typeerror, claiming it must be a string. fieldThree is nullable, so it *should* be allowed!
    }
  }
})
4 Replies
Prisma AI Help
Prisma AI Help•2mo ago
You've gone full bot mode! Let me fetch that AI answer for you ASAP. You're encountering a known limitation in Prisma. When you define a compound unique constraint that includes a nullable field (like your fieldThree), Prisma's generated input types require that field to be a non-null value (string), even though your schema allows null. This is because, in SQL, NULL is not considered equal to NULL, so unique constraints do not prevent duplicate rows where the nullable field is NULL. As a result, Prisma cannot guarantee that a query like upsert or findUnique will only ever match a single row if you use null in the where clause. That's why the type system enforces string instead of string | null, and passing null results in a type error and/or runtime error Generated CompoundUniqueInput type is incorrect if one of the compound types is Nullable, Optional field is required in @@unique and where clause and doesn't accept null, Compound unique with a nullable field should allow null. Workarounds: - Use findFirst or findMany instead of findUnique/upsert if you need to query with null values, but be aware these may return multiple results Generated CompoundUniqueInput type is incorrect if one of the compound types is Nullable. - Some users implement a workaround by using a dummy value (like an empty string or 0) instead of null, or by creating a generated column in the database that coalesces nulls to a default value and using that in the unique constraint Compound unique with a nullable field should allow null. - For upsert, you may need to use a transaction with findFirst + create/update, or use $queryRaw for more control Prisma.upsert() should support the value of null in where condition. Currently, there is no native support in Prisma for using null in compound unique where clauses. This is a known limitation and tracked as a feature request.
Zeke
ZekeOP•2mo ago
boo
Prisma AI Help
Prisma AI Help•2mo ago
✅ Great, thanks for letting us know that this solved your question!
Zeke
ZekeOP•2mo ago
it's not that strange of a thing to do just add it

Did you find this page helpful?