P
Prisma4mo ago
Nik

@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
nnd.j4mo 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
Nik
Nik4mo 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
nnd.j4mo ago
Many-to-many relations | Prisma Documentation
How to define and work with many-to-many relations in Prisma.
Nik
Nik4mo 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
Nik
Nik4mo ago
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...
Nik
Nik4mo 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.
Nik
Nik4mo 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:
No description
Nik
Nik4mo 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
Nik
Nik4mo ago
This is how I'm manually editing the indexes created by Prisma:
No description
Enzonaki
Enzonaki4mo ago
Create a Github issue Its your best bet I imagine so they can implement it
Nik
Nik4mo ago
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...