Prisma•2w ago

Migration creates Implicit relational tables in database for an explicit M-N relationship

I have created two models, Parameters and Tests. Also, there is a model that defines explicit M-N relationship between these models. When I migrate the schema to database, it is also creating a table "_ParameterToTest" in the database, that is usually created for implicit relationship. How do I avoid creating this database?

model Test {
  id                    String                  @id @default(uuid())
  name                  String
  description           String
  price                 Float
  current_price         Float
  created_at            DateTime
  updated_at            DateTime
  order_id              String?
  Parameters            Parameter[]
  TestsOfMembersInOrder TestsOfMembersInOrder[]
  ParametersToTests     ParametersToTests[]

  @@map("tests")
  @@schema("public")
}

model Parameter {
  id                 String              @id @default(uuid())
  name               String
  Parameter_category Parameter_category? @relation(fields: [categoryId], references: [id])
  categoryId         String
  Tests              Test[]
  ParametersToTests  ParametersToTests[]

  @@map("parameters")
  @@schema("public")
}

model ParametersToTests {
  test_id      String
  Test         Test      @relation(fields: [test_id], references: [id])
  parameter_id String
  Parameter    Parameter @relation(fields: [parameter_id], references: [id])

  @@id([test_id, parameter_id])
  @@map("parameter_test")
  @@schema("public")
}

model Test {
  id                    String                  @id @default(uuid())
  name                  String
  description           String
  price                 Float
  current_price         Float
  created_at            DateTime
  updated_at            DateTime
  order_id              String?
  Parameters            Parameter[]
  TestsOfMembersInOrder TestsOfMembersInOrder[]
  ParametersToTests     ParametersToTests[]

  @@map("tests")
  @@schema("public")
}

model Parameter {
  id                 String              @id @default(uuid())
  name               String
  Parameter_category Parameter_category? @relation(fields: [categoryId], references: [id])
  categoryId         String
  Tests              Test[]
  ParametersToTests  ParametersToTests[]

  @@map("parameters")
  @@schema("public")
}

model ParametersToTests {
  test_id      String
  Test         Test      @relation(fields: [test_id], references: [id])
  parameter_id String
  Parameter    Parameter @relation(fields: [parameter_id], references: [id])

  @@id([test_id, parameter_id])
  @@map("parameter_test")
  @@schema("public")
}

Solution:

Hi @Faiz Khatri 👋 In your schema, you have defined an explicit many-to-many relationship using the ParametersToTests model. However, you've also defined an implicit many-to-many relationship by including Parameters Parameter[] in the Test model and Tests Test[] in the Parameter model. To avoid creating the additional _ParameterToTest table, you need to remove these implicit relation fields and rely solely on your explicit relationship through the ParametersToTests model. ...

Jump to solution

3 Replies

Solution

Nurul•2w ago

model Test {
  id                    String                  @id @default(uuid())
  name                  String
  description           String
  price                 Float
  current_price         Float
  created_at            DateTime
  updated_at            DateTime
  order_id              String?
  TestsOfMembersInOrder TestsOfMembersInOrder[]
  ParametersToTests     ParametersToTests[]

  @@map("tests")
  @@schema("public")
}

model Parameter {
  id                 String              @id @default(uuid())
  name               String
  Parameter_category Parameter_category? @relation(fields: [categoryId], references: [id])
  categoryId         String
  ParametersToTests  ParametersToTests[]

  @@map("parameters")
  @@schema("public")
}

model ParametersToTests {
  test_id      String
  Test         Test      @relation(fields: [test_id], references: [id])
  parameter_id String
  Parameter    Parameter @relation(fields: [parameter_id], references: [id])

  @@id([test_id, parameter_id])
  @@map("parameter_test")
  @@schema("public")
}

model Test {
  id                    String                  @id @default(uuid())
  name                  String
  description           String
  price                 Float
  current_price         Float
  created_at            DateTime
  updated_at            DateTime
  order_id              String?
  TestsOfMembersInOrder TestsOfMembersInOrder[]
  ParametersToTests     ParametersToTests[]

  @@map("tests")
  @@schema("public")
}

model Parameter {
  id                 String              @id @default(uuid())
  name               String
  Parameter_category Parameter_category? @relation(fields: [categoryId], references: [id])
  categoryId         String
  ParametersToTests  ParametersToTests[]

  @@map("parameters")
  @@schema("public")
}

model ParametersToTests {
  test_id      String
  Test         Test      @relation(fields: [test_id], references: [id])
  parameter_id String
  Parameter    Parameter @relation(fields: [parameter_id], references: [id])

  @@id([test_id, parameter_id])
  @@map("parameter_test")
  @@schema("public")
}

Faiz KhatriOP•2w ago

ohh... That was a horrible mistake... didn't even think about checking the models while reading documentation 😶‍🌫️ Thank you so much @Nurul (Prisma)

Nurul•2w ago

No worries! Happy to help! 🙂

Gaming

Programming

Migration creates Implicit relational tables in database for an explicit M-N relationship