Updating rows on Table with multiple Many-to-Many relationships fails UNIQUE constraint with "duplicate entry ... for key"

[smndtrl]

Checklist

• [x] I have searched budibase discussions and github issues to check if my issue already exists

Hosting

• Self
  • Method: docker
  • Budibase Version: latest (as of 06.08.24)

Describe the bug Updating rows on Table with multiple Many-to-Many relationships fails UNIQUE constraint with "duplicate entry ... for key". Despite updating a relation between "Resource" and "Level2", the insert fails because there is already a unique "Resource" and "Level1" relation.

To Reproduce Steps to reproduce the behavior:

1. Setup a MySQL database with multiple many-many relationships on 1 table (see prisma schema)

   
   model Level1 {
   id Int @id @default(autoincrement())
   data String
   
   resources Resource[] @relation(name: "Level1ToResource")
   }

model Level2 { id Int @id @default(autoincrement()) data String

resources Resource[] @relation(name: "Level2ToResource") }

model Level3 { id Int @id @default(autoincrement()) data String

resources Resource[] @relation(name: "Level3ToResource") }

model Level4 { id Int @id @default(autoincrement()) data String

documents Document[] @relation(name: "Level4ToDocument") }

model Resource { id Int @id @default(autoincrement()) data String

l1 Level1[] @relation(name: "Level1ToResource") l2 Level2[] @relation(name: "Level2ToResource") l3 Level3[] @relation(name: "Level3ToResource") }

model Document { id Int @id @default(autoincrement()) data String

l4 Level4[] @relation(name: "Level4ToDocument") }

2. Create relationships in budibase
4. Create view for e.g. "Resource", "Level1", "Level2" and add sample data to Level1/Level2
5. Create resource, make link to Level1/Level2
6. Editing relationship fields now fails

**Expected behavior**
Relations are updated correctly and no duplicates are tried to be inserted

**Screenshots**
<img width="1309" alt="image" src="https://github.com/user-attachments/assets/512cbd67-65db-4493-a638-1d06d2d2021e">

[linear[bot]]

BUDI-8544 Updating rows on Table with multiple Many-to-Many relationships fails UNIQUE constraint with "duplicate entry ... for key"

[andz-bb]

hey @smndtrl, which datasource connector are you using for this? MySQL?

[smndtrl]

@andz-bb Yes, exactly. Sorry that I forgot to add it

[smndtrl]

For completeness I checked with Postgres 15.

Updating a field where the table has multiple references shows "Duplicate key value violates unique constraint."

For reference the prisma schema as well as manifest.json and db.txt

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["views"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Level1 {
  id Int @id @default(autoincrement())
  data String

  resources Level1ToResource[]
}

model Level2 {
  id        Int        @id @default(autoincrement())
  data String

  resources Level2ToResource[]
}

model Level3 {
  id        Int        @id @default(autoincrement())
  data String

  resources Level3ToResource[]
}

model Level4 {
  id        Int        @id @default(autoincrement())
  data String

  documents Level4ToDocument[]
}

model Resource {
  id       Int     @id @default(autoincrement())
  data String

  l1  Level1ToResource[]
  l2  Level2ToResource[]
  l3  Level3ToResource[]
}

model Level1ToResource {
  Level1 Level1 @relation(fields: [A], references: [id])
  A Int
  Resource Resource @relation(fields: [B], references: [id])
  B Int

  @@id([A, B])
  @@index([A, B])
  @@map("_Level1ToResource")
}

model Level2ToResource {
  Level2 Level2 @relation(fields: [A], references: [id])
  A Int
  Resource Resource @relation(fields: [B], references: [id])
  B Int

  @@id([A, B])
  @@index([A, B])
  @@map("_Level2ToResource")
}

model Level3ToResource {
  Level3 Level3 @relation(fields: [A], references: [id])
  A Int
  Resource Resource @relation(fields: [B], references: [id])
  B Int

  @@id([A, B])
  @@index([A, B])
  @@map("_Level3ToResource")
}

model Document {
  id       Int     @id @default(autoincrement())
  data String

  l4   Level4ToDocument[]
}

model Level4ToDocument {
  Level4 Level4 @relation(fields: [A], references: [id])
  A Int
  Document Document @relation(fields: [B], references: [id])
  B Int

  @@id([A, B])
  @@index([A, B])
  @@map("_Level4ToDocument")
}

manifest.json db.txt