P
Prisma•5mo ago
ALESSIO

Relations on non unique fields

Hi guys, i'm building a new db and i need it to be multilingual. in order to achieve that i've created a contents table wich will handle every text/image for each language 
model Contents {
  id         Int
  language   Languages
  content    String
  content_id Int
  type       ContentType
  challenges_names Challenges[] @relation("challenges_name_content")
  challenges_logo Challenges[] @relation("challenges_logo_content")
  challenges_badges Challenges[] @relation("challenges_badge_content")
  challenges_chapters_name Challenges_Chapters[]

  @@id([id])
  @@index([content_id])
  @@schema("system")
  @@unique([content_id, language])
}
model Contents {
  id         Int
  language   Languages
  content    String
  content_id Int
  type       ContentType
  challenges_names Challenges[] @relation("challenges_name_content")
  challenges_logo Challenges[] @relation("challenges_logo_content")
  challenges_badges Challenges[] @relation("challenges_badge_content")
  challenges_chapters_name Challenges_Chapters[]

  @@id([id])
  @@index([content_id])
  @@schema("system")
  @@unique([content_id, language])
}
Thanks to the content_id column i can join on the needed text and filter by language, that will come from the client basically id content_id language content 1 1 en hello 2 1 it ciao 3 1 es hola Now i can create my challenges modules and have a relation with Contents module like this 
model Challenges {
  id Int
  name_content Contents @relation("challenges_name_content", fields: [name_content_id], references: [content_id])  
  name_content_id Int
  logo_content Contents @relation("challenges_logo_content", fields: [logo_content_id], references: [content_id])
  logo_content_id Int
  badge_content Contents @relation("challenges_badge_content", fields: [badge_content_id], references: [content_id])
  badge_content_id Int
  color String
  secondary_color String
  super_challenge_json Json[]
  countries Json[]

  chapters Challenges_Chapters[]

  @@id([id])

  @@schema("system")
}
model Challenges {
  id Int
  name_content Contents @relation("challenges_name_content", fields: [name_content_id], references: [content_id])  
  name_content_id Int
  logo_content Contents @relation("challenges_logo_content", fields: [logo_content_id], references: [content_id])
  logo_content_id Int
  badge_content Contents @relation("challenges_badge_content", fields: [badge_content_id], references: [content_id])
  badge_content_id Int
  color String
  secondary_color String
  super_challenge_json Json[]
  countries Json[]

  chapters Challenges_Chapters[]

  @@id([id])

  @@schema("system")
}
but i get this error Error parsing attribute "@relation": The argument references must refer to a unique criterion in the related model. Consider adding an @unique attribute to the field content_id in the model Contents.Prisma for each relation constraint. How can i deal with this?
6 Replies
Prisma AI Help
Prisma AI Help•5mo ago
You selected the bot's instantaneous insights. Let me compile your answer right away! The error you're encountering is because Prisma requires that the field referenced in a @relation attribute must be unique in the related model. In your case, you're trying to reference content_id in the Contents model, but this field isn't unique by itself - it's only unique when combined with language as part of a composite unique constraint. There are a few ways to solve this: Option 1: Reference the composite unique fields You need to reference both fields that make up your unique constraint: 
model Challenges {
  id Int
  name_content Contents @relation("challenges_name_content", fields: [name_content_id, language], references: [content_id, language])  
  name_content_id Int
  language Languages
  // other fields...
  
  @@id([id])
  @@schema("system")
}
model Challenges {
  id Int
  name_content Contents @relation("challenges_name_content", fields: [name_content_id, language], references: [content_id, language])  
  name_content_id Int
  language Languages
  // other fields...
  
  @@id([id])
  @@schema("system")
}
This approach requires you to store the language in the Challenges model as well. Option 2: Make content_id unique If you want to keep your current structure, you could make content_id unique in the Contents model: 
model Contents {
  // other fields...
  content_id Int @unique
  // rest of model...
}
model Contents {
  // other fields...
  content_id Int @unique
  // rest of model...
}
However, this would change your data model significantly, as you could no longer have multiple languages for the same content_id. Option 3: Use a join model Create an intermediate model to handle the many-to-many relationship between challenges and contents. This is a common issue when working with relations in Prisma. As noted in the documentation, "The argument references must refer to a unique criterion in the related model" One-to-many relations. For MySQL specifically, Prisma enforces this uniqueness requirement since version 4.0.0 What's new in Prisma? (Q2/22). Given your multilingual requireme nts, Option 1 is likely the most appropriate solution as it maintains your data model while satisfying Prisma's constraints. 🤔 Alright, thanks for letting us know that there's still some help needed. A team member will reply to you shortly. In the meantime, please let us know any additional info, questions, or which parts of the answer may need clarification.
ALESSIO
ALESSIOOP•5mo ago
Prisma AI, i can't use both solution 1 or 2
Nurul
Nurul•5mo ago
Would adding a unique constraint on content_id not solve this? Is there a particular reason why you cannot add a unique constraint?
ALESSIO
ALESSIOOP•5mo ago
@Nurul (Prisma) content_id is not unique, because the same content_id has more languages like this: id content_id language content 1 1 en hello 2 1 it ciao 3 1 es hola So that i can query the correct text by content_id and the client's language And may i asked to be pinged when replied? I will reply back much faster, thank you for your help anyway
Nurul
Nurul•5mo ago
@ALESSIO Ah okay! I get it now. Can't you do something like this: 
model Challenges {
    id                   Int
    name_content         Contents @relation("challenges_name_content", fields: [name_content_id], references: [id])
    name_content_id      Int
    logo_content         Contents @relation("challenges_logo_content", fields: [logo_content_id], references: [id])
    logo_content_id      Int
    badge_content        Contents @relation("challenges_badge_content", fields: [badge_content_id], references: [id])
    badge_content_id     Int
    color                String
    secondary_color      String
    super_challenge_json Json[]
    countries            Json[]

    @@id([id])
}
model Challenges {
    id                   Int
    name_content         Contents @relation("challenges_name_content", fields: [name_content_id], references: [id])
    name_content_id      Int
    logo_content         Contents @relation("challenges_logo_content", fields: [logo_content_id], references: [id])
    logo_content_id      Int
    badge_content        Contents @relation("challenges_badge_content", fields: [badge_content_id], references: [id])
    badge_content_id     Int
    color                String
    secondary_color      String
    super_challenge_json Json[]
    countries            Json[]

    @@id([id])
}
Instead of referencing content_id, you reference the actual id field of the Contents model which is already unique?
ALESSIO
ALESSIOOP•5mo ago
the id of contents table is different for every row, this means that the same challenge must have more records for the same same challenge in order to reference a different content id every time. With the non unique content _id i can have multiple languages for the same text and only get the text based by the client locale If you want we can arrange a discord call and discuss about this, this might be a not so rare case @Nurul Hello @Nurul (Prisma) ?

Did you find this page helpful?