onDelete: SetNull on @@id() (composite ID) with optional and required value does not work?

[luap2703]

Lets say we have 3 models:

model Company {
company_id String @id
products Product[] ...
productAttributes ProductAttribute[] ...
}

model Product {
sku String

company_id String
company Company ...

attribute_id
productAttribute? @relation(fields: [attribute_id, company_id], references: [attribute_id, company_id], onDelete: SetNull)

@@id([sku, company_id])
}

model ProductAttribute {
attribute_id String

company_id String
company Company ...

products Product[]

@@id([attribute_id, company_id])
}

Now when I want to delete a ProductAttribute, it will ALWAYS FAIL, as it will try to set the company_id on Product also Null due to the onDelete: SetNull parameter. How can I overcome this? How can i make sure only attribute_id will be set null?

[nurul3101]

Hello @Paulo2703 👋

The onDelete: SetNull reference action will attempt to set all fields in relation to null when the referenced row is deleted which would cause an error as you shared.

How can i make sure only attribute_id will be set null?

I would recommend changing your schema file a bit like this:

model Company {
  company_id        String             @id
  products          Product[]
  productAttributes ProductAttribute[]
}

model Product {
  sku              String
  company_id       String
  company          Company           @relation(fields: [company_id], references: [company_id])
  attribute_id     String?
  productAttribute ProductAttribute? @relation(fields: [attribute_id], references: [attribute_id], onDelete: SetNull)

  @@id([sku, company_id])
}

model ProductAttribute {
  attribute_id String    @id
  company_id   String
  company      Company   @relation(fields: [company_id], references: [company_id])
  products     Product[]

  @@unique([attribute_id, company_id])
}

Now, the company_id relation between Product and Company is separate from the attribute_id relation between Product and ProductAttribute. Now if a ProductAttribute is deleted, only attribute_id in Product is set to null, while company_id remains intact.

Hope this helps!

Please let us know in case you have any further queries. If not, please consider marking the discussion as answered/closed.

[luap2703]

Hello @nurul3101 ,

thanks for your reply!
This is exactly how I changed the model now, by introducing a unique Id to the attribute model. BUT - this misses a bit the point. Let’s assume (which is my case) that all the data objects are from an external api with external values I cannot control. The attribute_id is not unique (only on a company level it is), so i cannot add a @id constraint to it.

the only way to overcome it was to introduce a custom id that is internal.

A better (and somehow more logical approach) would be to introduce a ondelete/onupdate prop for each part of the composite id, right? This way I could set attribute_id on product to null and company_id would be NoAction

[ludralph]

Hi there,

To keep our discussions organized and focused on the most relevant topics, we’re reviewing and tidying up our backlog. As part of this process, we’re closing discussions that haven’t had any recent activity and appear to be outdated.

If this discussion is still important to you or unresolved, we’d love to hear from you! Feel free to reopen it or start a new one with updated details.

For more details about our priorities and vision for the future of Prisma ORM, check out our latest blog post: https://www.prisma.io/blog/prisma-orm-manifesto.

Thank you for your understanding and being part of the community!