I want to use FK as id(as a composite key)
duri0214 opened this issue · 3 comments
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
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)
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")
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.