Prisma schema delete many to many

Hi, I have some troubles deleting a many to many relationship / data. Currently it says:

prisma:error
Invalid `prisma.templateTag.deleteMany()` invocation:


Foreign key constraint failed on the field: `TagsOnTemplate_tagId_fkey (index)`
❌ tRPC failed on template.delete:
Invalid `prisma.templateTag.deleteMany()` invocation:


Foreign key constraint failed on the field: `TagsOnTemplate_tagId_fkey (index)`

prisma:error
Invalid `prisma.templateTag.deleteMany()` invocation:


Foreign key constraint failed on the field: `TagsOnTemplate_tagId_fkey (index)`
❌ tRPC failed on template.delete:
Invalid `prisma.templateTag.deleteMany()` invocation:


Foreign key constraint failed on the field: `TagsOnTemplate_tagId_fkey (index)`

This is the related schema:

model TemplateTag {
  id        String           @id @default(cuid())
  tag       String           @unique
  templates TagsOnTemplate[] 
}

model Template {
  id          String           @id @default(cuid())
  name        String
  description String?
  visibility  String           @default("private")
  filename    String
  content     String
  createdAt   DateTime         @default(now())
  updatedAt   DateTime         @updatedAt
  user        User             @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId      String
  tags        TagsOnTemplate[]
}

model TagsOnTemplate {
  id         String      @id @default(cuid())
  template   Template    @relation(fields: [templateId], references: [id])
  templateId String
  tag        TemplateTag @relation(fields: [tagId], references: [id])
  tagId      String
}

model TemplateTag {
  id        String           @id @default(cuid())
  tag       String           @unique
  templates TagsOnTemplate[] 
}

model Template {
  id          String           @id @default(cuid())
  name        String
  description String?
  visibility  String           @default("private")
  filename    String
  content     String
  createdAt   DateTime         @default(now())
  updatedAt   DateTime         @updatedAt
  user        User             @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId      String
  tags        TagsOnTemplate[]
}

model TagsOnTemplate {
  id         String      @id @default(cuid())
  template   Template    @relation(fields: [templateId], references: [id])
  templateId String
  tag        TemplateTag @relation(fields: [tagId], references: [id])
  tagId      String
}

1 Reply

utdevOP•2y ago

And this is how I delete my data:

  delete: protectedProcedure
    .input(deleteTemplateSchema)
    .mutation(async ({ ctx, input }) => {
      const user = ctx.session.user

      if (!user.id) {
        throw new Error('Not authenticated')
      }

      // find all tagId from pivot table TagsOnTemplate and delete them inside the TemplateTag table
      const tagsOnTemplate = await ctx.prisma.tagsOnTemplate.findMany({
        where: {
          templateId: input.id,
        },
      })

      const tagIds = tagsOnTemplate.map(
        (tagOnTemplate: { tagId: string }) => tagOnTemplate.tagId
      )

      await ctx.prisma.tagsOnTemplate.deleteMany({
        where: {
          templateId: input.id,
        },
      })

      await ctx.prisma.templateTag.deleteMany({
        where: {
          id: {
            in: tagIds,
          },
        },
      })

      await ctx.prisma.template.delete({
        where: {
          id: input.id,
        },
      })

      return {
        status: 201,
        message: 'Template deleted successfully.',
      }
    }),

  delete: protectedProcedure
    .input(deleteTemplateSchema)
    .mutation(async ({ ctx, input }) => {
      const user = ctx.session.user

      if (!user.id) {
        throw new Error('Not authenticated')
      }

      // find all tagId from pivot table TagsOnTemplate and delete them inside the TemplateTag table
      const tagsOnTemplate = await ctx.prisma.tagsOnTemplate.findMany({
        where: {
          templateId: input.id,
        },
      })

      const tagIds = tagsOnTemplate.map(
        (tagOnTemplate: { tagId: string }) => tagOnTemplate.tagId
      )

      await ctx.prisma.tagsOnTemplate.deleteMany({
        where: {
          templateId: input.id,
        },
      })

      await ctx.prisma.templateTag.deleteMany({
        where: {
          id: {
            in: tagIds,
          },
        },
      })

      await ctx.prisma.template.delete({
        where: {
          id: input.id,
        },
      })

      return {
        status: 201,
        message: 'Template deleted successfully.',
      }
    }),

Updated the pivot table to this now

model TagsOnTemplate {
  id         String      @id @default(cuid())
  template   Template    @relation(fields: [templateId], references: [id], onDelete: Cascade)
  templateId String
  tag        TemplateTag @relation(fields: [tagId], references: [id], onDelete: Cascade)
  tagId      String
}

model TagsOnTemplate {
  id         String      @id @default(cuid())
  template   Template    @relation(fields: [templateId], references: [id], onDelete: Cascade)
  templateId String
  tag        TemplateTag @relation(fields: [tagId], references: [id], onDelete: Cascade)
  tagId      String
}

And my delete order is like this:

      await ctx.prisma.templateTag.deleteMany({
        where: {
          id: {
            in: tagIds,
          },
        },
      })

      await ctx.prisma.template.delete({
        where: {
          id: input.id,
        },
      })

      await ctx.prisma.tagsOnTemplate.deleteMany({
        where: {
          templateId: input.id,
        },
      })

      await ctx.prisma.templateTag.deleteMany({
        where: {
          id: {
            in: tagIds,
          },
        },
      })

      await ctx.prisma.template.delete({
        where: {
          id: input.id,
        },
      })

      await ctx.prisma.tagsOnTemplate.deleteMany({
        where: {
          templateId: input.id,
        },
      })

Seems to work, but feels a bit weird. Nah I am wrong it still acts flaky 😕

Gaming

Programming

Prisma schema delete many to many