Prisma•8mo ago

@unique on optional field

model Player {
  id        String    @id @default(auto()) @map("_id") @db.ObjectId
  steam_id  String    @unique
  ...
  user      User?     @relation(fields: [user_id], references: [id])
  user_id   String?    @db.ObjectId @unique
  ...
  @@map("players")
}

model Player {
  id        String    @id @default(auto()) @map("_id") @db.ObjectId
  steam_id  String    @unique
  ...
  user      User?     @relation(fields: [user_id], references: [id])
  user_id   String?    @db.ObjectId @unique
  ...
  @@map("players")
}

and

model User {
  id            String    @id @default(auto()) @map("_id") @db.ObjectId
  name          String?
  email         String?   @unique
  ...
  player        Player?
  ...
  @@map("users")
}

model User {
  id            String    @id @default(auto()) @map("_id") @db.ObjectId
  name          String?
  email         String?   @unique
  ...
  player        Player?
  ...
  @@map("users")
}

As you can see, user_id is an optional field. However the relation REQUIRES it to be marked as unique. In doing so, I get the following error: duplicate key { user_id: null } I imagine this will be an issue for excluded emails too, though I haven't run into it yet

12 Replies

nnd.j•8mo ago

Hey @Nik Is there any reason why user/user_id is optional? It makes sense to be required as the player should not exists without associated user. I believe the field cannot be both, optional and unique, if you want to keep it optional and still unique, you will be responsible for creating unique identifier for that entity

NikOP•8mo ago

that's actually not the case here. A user is tied to whether they've signed in. And a player is only linked/created when they start their first game. Both are optional So they have to be optional on both sides. So a player can be created without a user (witness another player ingame) and a user without a player (hasn't played a game yet)

nnd.j•8mo ago

Then I guess you need to create - relation https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/many-to-many-relations

Many-to-many relations | Prisma Documentation

How to define and work with many-to-many relations in Prisma.

NikOP•8mo ago

the issue is that fundamentally the relationship is one to one, and I'd prefer not to hack it with a many-many relationship that isn't accurate am I missing something? ty for the help

NikOP•8mo ago

https://github.com/prisma/prisma/issues/3387

GitHub

Support @unique on nullable fields for databases that support it ...

It would be great if we could take advantage of Postgres's capability to express uniqueness on not null values. Currently if I add the @unique constraint on a nullable field I can't have mo...

NikOP•8mo ago

https://www.prisma.io/docs/orm/reference/prisma-schema-reference#unique

Prisma Schema API | Prisma Documentation

API reference documentation for the Prisma Schema Language (PSL).

NikOP•8mo ago

yarn prisma db push
...
Error: MongoDB error
Kind: Command failed: Error code 11000 (DuplicateKey): Index build failed: 0008e6a8-ec9a-481b-901a-b4ff6d19ef32: Collection test-next.players ( 4b153292-5b85-4ed1-9f64-8e64be98fda0 ) :: caused by :: E11000 duplicate key error collection: test-next.players index: players_user_id_key dup key: { user_id: null }, labels: {}
   0: schema_core::state::SchemaPush
             at schema-engine/core/src/state.rs:433

yarn prisma db push
...
Error: MongoDB error
Kind: Command failed: Error code 11000 (DuplicateKey): Index build failed: 0008e6a8-ec9a-481b-901a-b4ff6d19ef32: Collection test-next.players ( 4b153292-5b85-4ed1-9f64-8e64be98fda0 ) :: caused by :: E11000 duplicate key error collection: test-next.players index: players_user_id_key dup key: { user_id: null }, labels: {}
   0: schema_core::state::SchemaPush
             at schema-engine/core/src/state.rs:433

db.contacts.createIndex(
   { email: 1 },
   { unique: true, partialFilterExpression: { email: { $exists: true } } }
);

db.contacts.createIndex(
   { email: 1 },
   { unique: true, partialFilterExpression: { email: { $exists: true } } }
);

In mongo / mongo drivers I can ignore null values like this.

NikOP•8mo ago

So according to my research this should be creating a sparse / partial index in MongoDB to support this. However upon prisma db push to a new empty database, I see the following keys:

NikOP•8mo ago

they're not marked sparse / partial which causes the issue. THE PROBLEM OPTIONAL @unique indexes are erroring with duplicate null/undefined values. This means that two records that omit an optionally @unique field will clash THE SOLUTION What Prisma should be doing is setting Sparse = true on any index of an optional @unique field But I am having to manually remember to do this for every index from an optional @unique If we mark an OPTIONAL field foo @unique, run: db.collection.createIndex( { foo: 1 }, { unique:true, sparse: true } ) If we mark a REQUIRED field foo @unique, run: db.collection.createIndex( { foo: 1 }, { unique:true ) ^ This is all prisma would have to do, currently it runs the second in both cases

NikOP•8mo ago

This is how I'm manually editing the indexes created by Prisma:

Enzonaki•8mo ago

Create a Github issue Its your best bet I imagine so they can implement it

NikOP•8mo ago

https://github.com/prisma/prisma/issues/23870#issue-2245535067

GitHub

[BUG] Prisma not supporting optional @unique indexes in MongoDB · I...

Bug description THE PROBLEM OPTIONAL @unique indexes are erroring with duplicate null/undefined values. This means that two records that omit an optionally @unique field will clash THE SOLUTIO...

Gaming

Programming

@unique on optional field