[relations & constraints] one-to-one relations, from example User to Profile, both side optional but db push error

[peterweb2005]

Bug description

as title:

from example
3rd block of the linked section
https://www.prisma.io/docs/concepts/components/prisma-schema/relations/one-to-one-relations#required-and-optional-1-1-relation-fields

boths sides optional

model User {
  id        Int      @id @default(autoincrement())
  profile   Profile? @relation(fields: [profileId], references: [id]) // references `id` of `Profile`
  profileId Int?     @unique // relation scalar field (used in the `@relation` attribute above)
}

model Profile {
  id   Int   @id @default(autoincrement())
  user User?
}

but when db push, failed push schema state, as error message

caused by :: E11000 duplicate key error collection: db.User index: User_profileId_key dup key: { profileId: null })
   0: migration_core::state::SchemaPush
             at migration-engine/core/src/state.rs:433

wont allow null

How to reproduce

Expected behavior

No response

Prisma information

// Add your schema.prisma

// Add your code using Prisma Client

Environment & setup

• OS:
• Database: MongoDB
• Node.js version: 18.12.1

Prisma Version

4.9.0

[peterweb2005]

my workaround, use one to many relation, one user to many "profiles"

to be clear, my case is not "profile" but "file"

[janpio]

Do you already have data in your database when you db push?

Unique indexes in MongoDB work a bit different, in as they only allow 1 NULL value per field in a collection: https://www.mongodb.com/docs/manual/core/index-unique/#unique-index-and-missing-field

If you already have multiple entries in the collection without data in that field, the unique index can not be created. We unfortunately can not express that explicitly in PSL - as there is either "required" or "optional", where optional normally means many entries can be empty/NULL on that field - but for MongoDB that is not correct.

[peterweb2005]

so, this is the cause?
which you opened the issue 4 years ago

#3419

[janpio]

Yes, I think that would be the related feature. As you can see not a lot of demand for it, so it never made the top of our priority list yet.