Prisma1: `onDelete: CASCADE` not working properly with required relation

[nikolasburk]

Describe the bug

The bug is described here.

To Reproduce

Deploy this datamodel:

type Loan {
  id: ID! @unique
  items: [LoanItem!]! @relation(name: "LoanItems", onDelete: CASCADE)
  by: User! @relation(name: "RequestedLoans")
  for: User! @relation(name: "ReceivedLoans")
}

type LoanItem {
  id: ID! @unique
  category: Category
  loan: Loan! @relation(name: "LoanItems")
}

type Category {
  id: ID! @unique
  name: String!
}

type User {
  id: ID! @unique
  name: String!
}

Create data such that:

• There is one Loan (let's call it A) with two LoanItems for the items relation
  
  
  
  • One LoanItem (X) has a relation for the category field
  
  
  • The other LoanItem (Y) has no relation for the category field, i.e. it's null
  
  

Then run the following mutation:

mutation {
  deleteManyLoans(
    where: {
      items_some: {
        category: null
      }
    }
  ) { count }
}

This should work but fails with the error: The change you are trying to make would violate the required relation 'LoanItems' between Loan and LoanItem"

Presumably Prisma tries to delete A first (i.e. before deleting X and Y) but then fails because X and Y have required relations to A.

Expected behavior

A, X and Y should be deleted.

[do4gr]

This is technically not a bug but a limitation on cascading deletes that we have because a deleteMany that then also cascades might put a lot of strain on the db and lead to deadlocks. DeleteMany does therefore not trigger cascading deletes.

A workaround would be to run the filter as a query and then run separate deletes on the returned ids. These should then trigger cascading deletes. Sorry for the confusion we'll try to document that limitation better!

[cmoses8626]

Don't think this is in the docs yet, is it @do4gr? Wasted a bunch of time this afternoon trying to figure this one out, thought I was nuts 😆

Thanks for the response above!

https://www.prisma.io/docs/1.28/datamodel-and-migrations/datamodel-MYSQL-knul/#using-the--argument-of-the--directive

[do4gr]

We just implemented this for the SQL connectors and will shortly merge this into alpha, that's why we did not update the docs. It should be available next week.

[JensMadsen]

@do4gr Is it released?

[do4gr]

This was merged into alpha 16 days ago and now made it's way into beta. It will be promoted to stable next week.