P
Prisma•2mo ago
megusta

Nullable unique field on Prisma

Hi, @everyone, I have a Prisma model on MongoDB. 
model StakeTx {
  txId           String  @id @map("_id")
  stakeId        String? @unique @db.ObjectId
  sourceAddress  String
  targetAddress  String
  amount         Int
  fee            Int
  confirmed      Boolean @default(false)

  createdAt DateTime?

  stake        Stake?        @relation(fields: [stakeId], references: [id])
  unstake      Unstake?

  @@index([txId, stakeId])
  @@index([sourceAddress, targetAddress])
  @@index([confirmed])
  @@map("stake_txs")
}
model StakeTx {
  txId           String  @id @map("_id")
  stakeId        String? @unique @db.ObjectId
  sourceAddress  String
  targetAddress  String
  amount         Int
  fee            Int
  confirmed      Boolean @default(false)

  createdAt DateTime?

  stake        Stake?        @relation(fields: [stakeId], references: [id])
  unstake      Unstake?

  @@index([txId, stakeId])
  @@index([sourceAddress, targetAddress])
  @@index([confirmed])
  @@map("stake_txs")
}
the stakeId field is nullable field. Now I can't run yarn prisma db push I got this error 
Kind: Command failed: Error code 11000 (DuplicateKey): Index build failed: 40f377f5-628e-4e6a-8439-4297d00661ca: Collection pivotal_db_staging.stake_txs ( c5460ee6-0849-45d0-8176-410b24afb3e3 ) :: caused by :: E11000 duplicate key error collection: pivotal_db_staging.stake_txs index: stake_txs_stakeId_key dup key: { stakeId: null }, labels: {}
Kind: Command failed: Error code 11000 (DuplicateKey): Index build failed: 40f377f5-628e-4e6a-8439-4297d00661ca: Collection pivotal_db_staging.stake_txs ( c5460ee6-0849-45d0-8176-410b24afb3e3 ) :: caused by :: E11000 duplicate key error collection: pivotal_db_staging.stake_txs index: stake_txs_stakeId_key dup key: { stakeId: null }, labels: {}
2 Replies
Prisma AI Help
Prisma AI Help•2mo ago
Skipping coffee for instant knowledge! Let's brew that AI answer now. The issue you're encountering is related to how MongoDB handles unique indexes on nullable fields. In MongoDB, a unique index treats null values as equal, which means you can only have one document with a null value for a field marked as unique. This behavior is different from some other databases like PostgreSQL, where multiple null values are allowed in a unique index. Prisma currently doesn't have built-in support for sparse unique indexes in MongoDB, which would allow multiple null values. To resolve this issue, you have a few options: 1. Make the stakeId field required (non-nullable) if it's appropriate for your data model. 2. Remove the @unique attribute from the stakeId field if uniqueness is not strictly necessary. 3. Use a workaround by creating a sparse unique index directly in MongoDB after pushing your schema. This isn't ideal as it requires manual intervention outside of Prisma. 4. Consider using an array for the relationship instead of a nullable field, as suggested in this GitHub issue comment. It's worth noting that this is a known limitation when using Prisma with MongoDB, and there are ongoing discussions about improving support for this use case. You can follow the progress on these related issues: - Support sparse unique indexes - MongoDB Index Management For now, you'll need to choose one of the workarounds mentioned above to proceed with your prisma db push command.
Nurul
Nurul•2mo ago
Hey 👋 Checking to see if you still need any assistance!

Did you find this page helpful?