Cannot use "set" when joining entities in an explicit ManyToMany table

[uncvrd]

Bug description

It appears that when creating an explicit many-to-many join table in Prisma, I cannot use the keyword set when joining related entities during an update. I receive the following error:

The change you are trying to make would violate the required relation 'ArtistReleaseToRelease' between the ArtistRelease and Release models."

Given the following schema.prisma, I have an Artist a Release and an ArtistRelease join table. This was necessary because I needed to associate a verified property on the join table:

model Release {
  id        Int      @id @default(autoincrement())
  title     String
  artists   ArtistRelease[]
}

model Artist {
  id        Int     @id @default(autoincrement())
  name      String
  releases    ArtistRelease[]
}

model ArtistRelease {
  artist Artist @relation(fields: [artistId], references: [id])
  artistId Int
  release Release @relation(fields: [releaseId], references: [id])
  releaseId Int
  verified Boolean @default(false)

  @@id([artistId, releaseId])
}

However, when I try and use the update method and set the artists (since all of them might change or be replaced), I run the following command and receive the error above:

const response = await ctx.db.release.update({
  where: {
    id: 1
  },
  data: {
    artists: {
      set: {
        artistId_releaseId: {
          artistId: 1,
          releaseId: 1
        }
      }
    }
  }
})

I attempted to use set on an implicit join table I have in my project and it worked as expected. My only idea is that I need to make both the release and artist nullable in ArtistRelease and then create a id primary key for that instead of using a composite primary key but going off the docs, I didn't see an explicit join table defined this way so I wasn't sure. Any insight here would be appreciated!

Expected behavior

I expect to be able to use the set method when using an explicit many-to-many join table in Prisma.

Environment & setup

• OS: MacOS
• Database: PostgresSQL
• Node.js version: v12.16.2
• Prisma version:

@prisma/cli          : 2.9.0
@prisma/client       : 2.9.0
Current platform     : darwin
Query Engine         : query-engine 369b3694b7edb869fad14827a33ad3f3f49bbc20 (at node_modules/@prisma/cli/query-engine-darwin)
Migration Engine     : migration-engine-cli 369b3694b7edb869fad14827a33ad3f3f49bbc20 (at node_modules/@prisma/cli/migration-engine-darwin)
Introspection Engine : introspection-core 369b3694b7edb869fad14827a33ad3f3f49bbc20 (at node_modules/@prisma/cli/introspection-engine-darwin)
Format Binary        : prisma-fmt 369b3694b7edb869fad14827a33ad3f3f49bbc20 (at node_modules/@prisma/cli/prisma-fmt-darwin)
Studio               : 0.296.0

---

EDIT: After more digging, does this have to do with cascade deletes not working yet? #2328

[pantharshit00]

Hey @uncvrd

Sorry for the late response here. I am unable to reproduce this in the latest version of Prisma.

Can you please try this again? If you can still reproduce this in the latest version, please share a sql dump of your database. That will help a lot.

[exs2]

Hi @pantharshit00 in my case, the code runs fine but no data inserted into ManyToMany table.

[pantharshit00]

Hey @exs2

Can you please provide a reproduction for that bug?

[PeerasakRattanamanee]

Hi @pantharshit00

I'm now encounter the same error as @uncvrd. reproduce repo https://github.com/PeerasakRattanamanee/4032

[sarakusha]

Workaround: upsert & deleteMany

const newArtists = [1,2,3,4,5,6,7];
const response = await ctx.db.release.update({
  where: {
    id: 1
  },
  data: {
    artists: {
      upsert: newArtitst.map(artistId => ({
         where: { artistId_releaseId: { artistId, releaseId: 1} },
         update: {...},
         create: { artist: { connect: { id: artistId }}},
      })),
      deleteMany: {
          releaseId: 1,
          artistId: { notIn: newArtists },
      },
    }
  }
})

[pantharshit00]

Thanks for the reproduction @PeerasakRattanamanee. I can reproduce this now. Looks like our engine checking logic is applying wrong check when changing ids.

Internal note: Use this reproduction https://github.com/PeerasakRattanamanee/4032, use the seed function to initialise data.

[pantharshit00]

This is still relevant in 2.21.2 and reproducible. Mostly related cascade delete work.

[onadeemdev]

Was this issue ever solved? How does setting values in an explicit join table relate to referential integrity?

[Jolg42]

I can reproduce using on 4.3.1 (and with our internal dev version 4.4.0-dev.30) with the repo https://github.com/PeerasakRattanamanee/4032

ts-node main.ts   
[ { roleId: 1, userId: 1 } ]
(node:40359) UnhandledPromiseRejectionWarning: Error: 
Invalid `prisma.user.update()` invocation in
/Users/j42/Repros/4032/main.ts:11:34

   8 const existing = await prisma.user_Role.findMany();
   9 console.log(existing);
  10 
→ 11 const test = await prisma.user.update(
The change you are trying to make would violate the required relation 'UserToUser_Role' between the `User` and `User_Role` models.
    at RequestHandler.handleRequestError (/Users/j42/Repros/4032/node_modules/@prisma/client/runtime/index.js:29909:13)
    at RequestHandler.request (/Users/j42/Repros/4032/node_modules/@prisma/client/runtime/index.js:29892:12)
    at async PrismaClient._request (/Users/j42/Repros/4032/node_modules/@prisma/client/runtime/index.js:30864:16)