prisma: relationMode = `prisma`: Error validating: A self-relation must have `onDelete` and `onUpdate` referential actions set to `NoAction`

Not sure if this has something to do with this issue, but I’m getting an issue when trying to delete a self-relation. When I try deleting all nested children in a 1-M self-relation, I get the error below. However, using a raw query works.

Schema:

model Topic {
  id            Int          @id @default(autoincrement())
  parentTopicId Int?
  parentTopic   Topic?       @relation("Subtopic", fields: [parentTopicId], references: [id], onDelete: NoAction, onUpdate: NoAction)
  subTopics     Topic[]      @relation("Subtopic")
}

Prisma delete query (trying to delete all subtopics):

await prisma.topic.deleteMany({
    where: { NOT: { parentTopicId: null } },
});

Error message:

The change you are trying to make would violate the required relation 'Subtopic' between the `Topic` and `Topic` models.

Solution (raw query):

await prisma.$queryRaw`
    DELETE FROM topics T 
    WHERE T. parentTopicId IS NOT NULL
`;

Is there any current solution to this without using a raw query?

@jkomyno did some sleuthing, and I am here with the simple job to report his findings:

This is currently working as designed, although we are lacking the documentation for it.

https://github.com/prisma/prisma/issues/9931 was an issue triggered by a user having a self relation, and it then crashing Prisma Client. This was fixed by expanding our SQL Server and MongoDB cyclic ref actions detections to these cases as well in this PR: https://github.com/prisma/prisma-engines/pull/2415

Here is the explanation why this is needed:

Our query engine doesn’t do JIT with the queries, so all the query planning is done first and only after that executed. This means a cascade that cycles back to itself will just generate a query until stack overflow, a bit like what Microsoft SQL Server would do… Therefore the first action will be that we just prevent cascading cycles completely if Prisma handles referential integrity… Source

And the “workaround”:

The thing you can do right now is cut the cycle with NoAction for updates and deletes, then handling the deletion in your application code.

Would this work for you @baptisteArno?

Yes, totally fine! Thank you for the investigation 👌

I tried the NoAction for onDelete and for onUpdate but I’m still getting this error. (Provider MongoDB)

Error parsing attribute "@relation": The type of the field `replyMessageId` in the model `CommunityMessage` is not matching the type of the referenced field `id` in model `CommunityMessage`.

My Model

model CommunityMessage {
  id        String   @id @default(auto()) @map("_id") @db.ObjectId
  body      String?
  image     String?
  createdAt DateTime @default(now())

  seenIds String[] @db.ObjectId
  seen    User[]   @relation("ChannelSeen", fields: [seenIds], references: [id])

  channelId String  @db.ObjectId
  channel   Channel @relation(fields: [channelId], references: [id], onDelete: Cascade)

  replyMessageId Int?    @unique
  replyMessage   CommunityMessage?   @relation("reply", fields: [replyMessageId], references: [id], onDelete: NoAction, onUpdate: NoAction)
  replies CommunityMessage[]   @relation("reply")

  senderId String @db.ObjectId
  sender   User   @relation(fields: [senderId], references: [id], onDelete: Cascade)
  

}

I would love if someone could help me out.

Now when I run npx prisma format I get this error

Error parsing attribute "@relation": The type of the field `replyMessageId` in the model `CommunityMessage` is not matching the type of the referenced field `id` in model `CommunityMessage`.

Hello, I have almost the same problem. Have you managed to solve it somehow?

model Post {
  id          String    @id @default(cuid())
  title       String
  content     Json?
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
  subreddit   Subreddit @relation(fields: [subredditId], references: [id])
  subredditId String

  author   User   @relation(fields: [authorId], references: [id])
  authorId String

  comments Comment[]
  votes     Vote[]
}

model Comment {
  id        String   @id @default(cuid())
  text      String
  createdAt DateTime @default(now())
  author    User     @relation(fields: [authorId], references: [id])
  authorId  String
  post      Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  postId    String

  replyToId String?
  replyTo   Comment?  @relation("ReplyTo", fields: [replyToId], references: [id], onDelete: NoAction, onUpdate: NoAction)
  replies   Comment[] @relation("ReplyTo")

  votes     CommentVote[]
  commentId String?
}

I tried the NoAction for onDelete and for onUpdate but I’m still getting this error. (Provider MongoDB)

Error parsing attribute "@relation": The type of the field `replyMessageId` in the model `CommunityMessage` is not matching the type of the referenced field `id` in model `CommunityMessage`.

My Model

model CommunityMessage {
  id        String   @id @default(auto()) @map("_id") @db.ObjectId
  body      String?
  image     String?
  createdAt DateTime @default(now())

  seenIds String[] @db.ObjectId
  seen    User[]   @relation("ChannelSeen", fields: [seenIds], references: [id])

  channelId String  @db.ObjectId
  channel   Channel @relation(fields: [channelId], references: [id], onDelete: Cascade)

  replyMessageId Int?    @unique
  replyMessage   CommunityMessage?   @relation("reply", fields: [replyMessageId], references: [id], onDelete: NoAction, onUpdate: NoAction)
  replies CommunityMessage[]   @relation("reply")

  senderId String @db.ObjectId
  sender   User   @relation(fields: [senderId], references: [id], onDelete: Cascade)
  

}

I would love if someone could help me out.

Hi @jkomyno , I explained here. Restrict throws error in my case cuz it asks me to handle related rows (I don’t quite remember the error; i think it’s error 2014 “The change you are trying to make would violate the required relation ‘ChildToParent’ between the Child and Parent models.”). I need a Nothing relationMode to handle the onUpdate and onDelete checking myself.