PrismaP
Prisma•14mo ago
Faiz Khatri

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")
}
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.

Something like this should work
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")
}
Jump to solution
Was this page helpful?