Prisma•5mo ago

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.OP•5mo ago

Bump Bump Bump

moosthuizen•5mo 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.

moosthuizen•5mo 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.OP•5mo 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!

moosthuizen•5mo ago

no prob, would be curious to hear if that works. Let me know

ncls.OP•5mo 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...😅

Gaming

Programming

How to reference/store a joined primary key?