Question 138724 in Mysql Question posted in
The official MySQL documentation can be found here.

Can optional foreign keys in MySQL-Prisma be set to nullable?

I am trying to update an optional foreign key in prisma to null, but it gives me an error that value null is not expected but the “undefined” can be done as a value. If try from MySQL to set it as null it works.
If I try to set undefined(which I want NULL not undefined), if previously a value was set, if i want to set undefined the value persist)

Vehicle Prisma Schema

     model Vehicle {
          id                        String                  @id @default(uuid())
          vehicle_model_category_id String?
          Vehicle_Model_Category    Vehicle_Model_Category? @relation(fields: [vehicle_model_category_id], references: [id], onUpdate: Cascade, onDelete: Cascade)
     }
     
  model Vehicle_Model_Category {
  id                      String               @id @default(uuid())
  name                    String               @db.VarChar(255)
 ....
  Vehicle                 Vehicle[]
}

Here one vehicle model might not have a category model, and normally can be null because also is optional in the schema.
Relationship between Vehicle_Model_Category(1) -> Vehicle_Model(Many)

  const vehicle = await prisma.vehicle.update({
      where: {
        id,
      },

      data:{
      Vehicle_Model_Category: null,
      }
 });

Error that I get is:

Argument Vehicle_Model_Category for data.Vehicle_Model_Category must not be null. Please use undefined instead.

I try manually to do it on MySQL to update on Mysql, it works but from prisma does not work.

Tags:

2

Answers


  1. 0

    Prisma differentiates between null and undefined. null is a value while undefined means do nothing. See this section of the documentation. Setting a field value to undefined is the same as not including the field in the update query at all. From the error message, prisma is expecting you to set it to undefined instead of null.

    For example, say we have this schema where a User has many posts but a Post may or may not have a user associated with it. Notice the optional relation author and the relation scalar authorId

    
model User {
  id    Int    @id @default(autoincrement())
  posts Post[]
}

model Post {
  id       Int   @id @default(autoincrement())
  author   User? @relation(fields: [authorId], references: [id])
  authorId Int?
}

    If we write a script to create a new post and set the optional relation author to null like below

    const post = await prisma.post.create({
    data: {
      author: null
    }
  })

    We’ll get an error message Argument author for data.author must not be null. Please use undefined instead. which is similar to the error message you have.

    I’ll update the script to set author relation field to undefined

    const post = await prisma.post.create({
    data: {
      author: undefined
    }
  })

    We’ll get this output

    { id: 2, authorId: null }
    Login or Signup to reply.
  2. 0

    An example of how you can update the Vehicle_Model_Category field to null using the undefined value (You’ll have to temporarily disable the foreign key relationship):

    const vehicle = await prisma.vehicle.update({
  where: {
    id,
  },
  data: {
    Vehicle_Model_Category: {
      disconnect: true,
    },
  },
});

    Also, regarding the Null and Undefined shenanigans, and Prisma is an ORM (JavaScript), and here Undefined is a datatype and conversions can be tricky and often evaluated by case-by-case basis, like here. Here’s an official source for it — Null and Undefined – Prisma Docs.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search