P
Prisma•2y ago
ncls.

How to reference/store a joined primary key?

I have a program that imports data from a CSV file into the database using Prisma. In the Excel the rows have something similar to an ID, a category and other non-important stuff. Since the ID is not unique in this CSV, I created a joined primary key in my schema file (@@id([category, id])). Is there any way I can reference this joined primary key in a relation? E.g.: 
model FirstEntity {
  id        Int
  category  String
  relations Relation[]

  @@id([category, id])
}

model Relation {
  id         Int         @id @default(autoincrement())
  relationId String
  relation   FirstEntity @relation(fields: [relatesToId], references: [category, id])
}
model FirstEntity {
  id        Int
  category  String
  relations Relation[]

  @@id([category, id])
}

model Relation {
  id         Int         @id @default(autoincrement())
  relationId String
  relation   FirstEntity @relation(fields: [relatesToId], references: [category, id])
}
6 Replies
ncls.
ncls.OP•2y ago
Bump Bump Bump
mq
mq•2y ago
@ncls. See this page of the documentation: https://www.prisma.io/docs/orm/prisma-client/special-fields-and-types/working-with-composite-ids-and-constraints You can give a name to a composite key (what you referred to as the joined key). I'm not sure if you can then use that name to create the relation, but it's worth a try: 
model FirstEntity {
  id        Int
  category  String
  relations Relation[]

  @@id("composite_key_1", [category, id])
}

model Relation {
  id                   Int         @id @default(autoincrement())
  firstEntityId        Int
  firstEntityCategory  String
  relation             FirstEntity @relation(fields: [firstEntityId, firstEntityCategory], references: [composite_key_1])
}
model FirstEntity {
  id        Int
  category  String
  relations Relation[]

  @@id("composite_key_1", [category, id])
}

model Relation {
  id                   Int         @id @default(autoincrement())
  firstEntityId        Int
  firstEntityCategory  String
  relation             FirstEntity @relation(fields: [firstEntityId, firstEntityCategory], references: [composite_key_1])
}
Working with compound IDs and unique constraints (Concepts) | Prism...
How to read, write, and filter by compound IDs and unique constraints.
mq
mq•2y ago
Unless there's a strong reason not to, you can also consider generating a new primary key, while still enforcing a unique constraint with @@unique. Best of both worlds.
ncls.
ncls.OP•2y ago
I was on this page like 200 times and somehow always managed to miss that...😭 I will try and see if it works tomorrow. Thank you!
mq
mq•2y ago
no prob, would be curious to hear if that works. Let me know
ncls.
ncls.OP•2y ago
Generating a new key doesn't work for my case since the values come from CSV files and should be updated if they already exist (using upsert) Will do. Thanks again for the hint. Idk how I missed that all the time...😅

Did you find this page helpful?