Can you make email String also optinal, so it can be NULL in the database?
Does that solve your problem?

Also faced the same problem. Setting the field (email in this case) to optional isn't a solution when the field itself should be mandatory but only the relation should be optional.
Shouldn't the ? on the user User? field in this example make the relation itself optional, therefore preventing the constraint from being created?

This issue is similar to #8447 which is supposedly fixed by prisma/prisma-engines#2221 but the problem with that solution is that, from what I understood, it involves disabling native foreign keys completely, which is not always desirable.

Anecdotally, I am experiencing this too. However, my DB is still in development.

It appears that I was able to resolve this by nuking the DB's tables, and re-running migrate dev from an empty DB. This appears to have resolved the type-errors in the generated prisma I was seeing after making a previously required 1:M relationship optional.

This indicates to me that there's probably a bug in the migrate + generate pipeline that relies on DB introspection.

@janpio that might solve the issue — but email is not optional, it's the relationship that's optional. I shouldn't have to allow for nullability on a column here as a workaround.

How do you express an optional relationship in SQL without making the underlying field optional?

Prisma 4.14 is creating a foreign key even when making both the field and relationship optional

Context:
I have crawled product data from multiple shops, but not all products are always linked to a specific shop because I don't want to monitor all shops. Sometimes, users only want to crawl a bunch of URLs from different shops. However, I still need to store those products. Making both the shop_id field and the shop relationship optional still creates a foreign key in the product table.
schema:

model shop {
  id                           Int       @id
  name                         String
  url                          String?
  products                     product[]
}

model product {
  id              Int  @id @default(autoincrement())
  shop_id Int?
  shop    shop? @relation(fields: [shop_id], references: [id])
}

For Prisma currently "relation = foreign key".

Any news on this? I can't imagine this being an edge case. Optional relations is quite common.

UPDATE: sorry, at least in my case it was a wrong assumption, I had another issue. When you expect the relation to be optional, you should make sure to have the fields value really set to null when doing the create().
So in our case, everything seems to be working as expected, now.

I'm running into this, are there any recommendations? I agree with @bcnichols3, in my case the scalar field is required but the relationship is optional

@bcnichols3 The relation between these two models exists, or it does not exist. There is no in between. It's just that sometimes it is not used.
If it exists, then you have to model it and Prisma will represent that with a foreign key.
If the scalar field the relation is using is null, then there will be no relation to follow.

In your case you can achieve that by moving the @relation to the other side:

model User {
  id     String  @id @default(cuid())
  email  String? @unique
  author Author? @relation(fields: [email], references: [email])
}

model Author {
  id    String @id @default(cuid())
  email String @unique
  name  String

  user User? 
}

Then all Authors have to have an email address. Not all Users need to have an email, but the ones that do, have to match an existing Author.

(If that is not what you want to model, please describe it again so we can see how that can be express in a relational database)

@dangnhdev What is the problem with the foreign key in this case? It is what is needed to guarantee referential integrity and actions for when the relation is actually used.

@samualtnorman What is your use case - which situation do you want to model?

hi @janpio i am currently facing this error too.
This is my table.

model GeofenceMapping {
  id         Int         @id @default(autoincrement())
  uuid       String      @unique @default(uuid()) @db.Uuid
  geofenceId String      @map("geofence_id") @db.Uuid
  entityType String      @map("entity_type") @db.VarChar(64) // For example, 'user' or 'department' or 'company'
  entityId   String?      @map("entity_id") @db.Uuid // The ID of the associated employee/department/company
  geofence   Geofence    @relation(fields: [geofenceId], references: [uuid])
  user       User?       @relation(fields: [entityId], references: [uuid], map: "user_mapping")
  department Department? @relation(fields: [entityId], references: [uuid], map: "department_mapping")
  company    Company?    @relation(fields: [entityId], references: [uuid], map: "company_mapping")

  createdAt DateTime  @default(now()) @map("created_at") @db.Timestamp()
  updatedAt DateTime  @default(now()) @updatedAt @map("updated_at") @db.Timestamp()
  deletedAt DateTime? @map("deleted_at") @db.Timestamp()

  @@unique([entityType, entityId, geofenceId])
  @@index([uuid])
  @@index([entityId])
  @@index([entityType, entityId])
  @@map("geofence_mapping")
}

my query .

    //3. create geofence
    const geofence = await this.geofenceRepository.createGeofence({
      data: {
        lat: createGeofenceDto.lat,
        long: createGeofenceDto.long,
        radius: createGeofenceDto.radius,
        address: createGeofenceDto.address,
        title: createGeofenceDto.title,
      },
    });
    
    //4. map geofence to company
    const mappedCompany = await this.geofenceRepository.createGeofenceMapping({
      data: {
        entityType: ENTITY.COMPANY,
        geofence: {
          connect: {
            uuid: geofence.uuid,
          },
        },
        company: {
          connect: {
            uuid: createGeofenceDto.companyId,
          },
        },
      },
    });

and i am getting this error.

prisma:error 
Invalid `this.prisma.geofenceMapping.create()` invocation in
/Users/ice/Documents/work/st-company-management/src/modules/geofence/geofence.repository.ts:34:40

  31 }
  32 
  33 async createGeofenceMapping(options: Prisma.GeofenceMappingCreateArgs) {
→ 34   return this.prisma.geofenceMapping.create({
         data: {
           entityType: "company",
           geofence: {
             connect: {
               uuid: "0486f7f0-aee7-4ca1-b6a6-b30d36957565"
             }
           },
           company: {
             connect: {
               uuid: "20e802ec-0dbc-42d7-b39e-47df1799e8f1"
             }
           },
           department: null,
       +   user: {
       +     create: UserCreateWithoutGeofenceMappingInput | UserUncheckedCreateWithoutGeofenceMappingInput,
       +     connectOrCreate: UserCreateOrConnectWithoutGeofenceMappingInput,
       +     connect: UserWhereUniqueInput
       +   }
         }
       })
       Argument `user` must not be null.

Any idea why it is enforcing user_mapping foreign key, i thought it is meant be optional ? your help will be greatly appreciated

see #18058 (comment).
currently the prisma client only respects the optionality on the scalar field despite what the generated type declarations say.
the current verdict from the prisma team is that your database is designed wrong and needs refactoring. if that's not an option, I have a fork but it's not currently in an easily distributable state.

@samualtnorman ooh thank you. I removed the relationships already and I'll do the inserts directly. Prisma relationships seem to be a bottleneck or hindrance sometimes.

verdict from the prisma team is that your database is designed wrong and needs refactoring.

Our verdict is that Prisma is not designed to work with such databases. Our relationships are mapped to foreign keys, and foreign keys tell Prisma where a relation exists.

We usually refer to relations without foreign keys as "weak relations", here is a feature request for them: #7351

any news on this one?