I want to use FK as id（as a composite key）

[duri0214]

Problem

When I set a composite key that includes FK as an ID, the ID violates the unique key.
Even if you create a composite key, a unique constraint is created on the FK, so duplicate FKs are not allowed.
I think it is possible to have a composite key like this in business.

classDiagram
  class areas {
     id: INT
     name: string
  }
  class weather_forecast {
      date: Date
     temperature: INT
     area_id: FK
  }
  weather_forecast --> areas : areas_id

CREATE TABLE `weather_forecasts` (
  `areas_id` VARCHAR(191) NOT NULL,
  `date` VARCHAR(191) NOT NULL,
  `temperature` INTEGER NOT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NULL,
  
  UNIQUE INDEX `weather_forecasts_areas_id_key` (`areas_id`),
  UNIQUE INDEX `weather_forecasts_areas_id_date_key` (`areas_id`, `date`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Suggested solution

Ability to create a composite key of date and xxx_id

Alternatives

Additional context

[janpio]

What is the Prisma context of your question? What Prisma schema do you get when you introspect your database that has that behavior?

(Aside: The SQL you are sharing does not show any foreign key, so does not match the ERD or the issue title)

[duri0214]

thank you for response :)
This is what it looks like when written in prisma schema.

model Areas {
  id String @id
  name String
  weatherForecasts WeatherForecasts[]
}
model WeatherForecasts{
  areas Areas @relation(fields: [areasId], references: [id], onDelete: Cascade)
  areasId String @unique @map("areas_id")
  date String
  temperature: Int
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime? @updateAt @map("updated_at")
}

@@unique([date, areasId])
@@map("weather_forecasts")

[janpio]

That is not a valid Prisma schema (for multiple reasons, we do not support : after the field name and no @@unique or @@map outside of models).

Can you please clarify your issue or question? Thanks.

[duri0214]

I'm sorry. Since it was extracted from the project code and arranged, it may contain syntax errors.
I talked with the development team and decided to do what we can to resolve the issue. That is to create an id column in the weather forecast table. Although there is a useless id column, I was able to solve the problem.

I will try to close the issue.
thank you :)

model Areas {
  id String @id
  name String
  weatherForecasts WeatherForecasts[]
}
model WeatherForecasts{
+ id String @id @default(cuid())
  date String
  areas Areas @relation(fields: [areasId], references: [id], onDelete: Cascade)
-  areasId String @unique @map("areas_id")
+ areasId String @map("areas_id")
  temperature Int
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime? @updateAt @map("updated_at")
}

@@unique([date, areasId])
@@map("weather_forecasts")