Foreign key relation results in erroneous second migration #23043
Description
Bug description
In a case where table B has two foreign key relations to table A, the first migration generation via prisma migrate dev reset is correct, but running it again (without changing the schema) wants to create an erroneous second migration
How to reproduce
Add the following to your schema.prisma
model prisma_bug_1 {
id1 BigInt
id2 BigInt
prisma_bug_2_a prisma_bug_2[] @relation("a")
prisma_bug_2_b prisma_bug_2[] @relation("b")
@@id([id1, id2])
}
model prisma_bug_2 {
id BigInt @id
prisma_bug_1_id1 BigInt
prisma_bug_1_id2 BigInt
prisma_bug_1_a prisma_bug_1 @relation("a", fields: [prisma_bug_1_id1, prisma_bug_1_id2], references: [id1, id2], map: "prisma_bug_1_a_fk")
prisma_bug_1_b prisma_bug_1? @relation("b", fields: [prisma_bug_1_id1, prisma_bug_1_id2], references: [id1, id2], map: "prisma_bug_1_b_fk")
}
this is based on something we hit in a context where it made sense, as these things go
running prisma migrate dev reset, the first migration it creates is correct. for reference:
-- CreateTable
CREATE TABLE "prisma_bug_1" (
"id1" BIGINT NOT NULL,
"id2" BIGINT NOT NULL,
CONSTRAINT "prisma_bug_1_pkey" PRIMARY KEY ("id1","id2")
);
-- CreateTable
CREATE TABLE "prisma_bug_2" (
"id" BIGINT NOT NULL,
"prisma_bug_1_id1" BIGINT NOT NULL,
"prisma_bug_1_id2" BIGINT NOT NULL,
CONSTRAINT "prisma_bug_2_pkey" PRIMARY KEY ("id")
);
-- AddForeignKey
ALTER TABLE "prisma_bug_2" ADD CONSTRAINT "prisma_bug_1_a_fk" FOREIGN KEY ("prisma_bug_1_id1", "prisma_bug_1_id2") REFERENCES "prisma_bug_1"("id1", "id2") ON DELETE RESTRICT ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE "prisma_bug_2" ADD CONSTRAINT "prisma_bug_1_b_fk" FOREIGN KEY ("prisma_bug_1_id1", "prisma_bug_1_id2") REFERENCES "prisma_bug_1"("id1", "id2") ON DELETE RESTRICT ON UPDATE CASCADE;
however, running the migrate command again, again with no change to the schema, you get the following:
Drift detected: Your database schema is not in sync with your migration history.
The following is a summary of the differences between the expected database schema given your migrations files, and the actual schema of the database.
It should be understood as the set of changes to get from the expected schema to the actual schema.
[*] Changed the `prisma_bug_2` table
[*] Renamed the foreign key "prisma_bug_1_b_fk" to "prisma_bug_1_a_fk"
the migration it creates
-- RenameForeignKey
ALTER TABLE "prisma_bug_2" RENAME CONSTRAINT "prisma_bug_1_b_fk" TO "prisma_bug_1_a_fk";
which then of course immediately fails to apply
Database error code: 42710
Database error:
ERROR: constraint "prisma_bug_1_a_fk" for relation "prisma_bug_2" already exists
DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState(E42710), message: "constraint \"prisma_bug_1_a_fk\" for relation \"prisma_bug_2\" already exists", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("pg_constraint.c"), line: Some(668), routine: Some("RenameConstraintById") }
Expected behavior
- a migration created from a schema and applied should be stable. immediately running reset again should never lead to a new migration being created
- in a case where you have custom foreign keys (in this case, it is required) it should properly detect that there are two and not try to rename it
Prisma information
model prisma_bug_1 {
id1 BigInt
id2 BigInt
prisma_bug_2_a prisma_bug_2[] @relation("a")
prisma_bug_2_b prisma_bug_2[] @relation("b")
@@id([id1, id2])
}
model prisma_bug_2 {
id BigInt @id
prisma_bug_1_id1 BigInt
prisma_bug_1_id2 BigInt
prisma_bug_1_a prisma_bug_1 @relation("a", fields: [prisma_bug_1_id1, prisma_bug_1_id2], references: [id1, id2], map: "prisma_bug_1_a_fk")
prisma_bug_1_b prisma_bug_1? @relation("b", fields: [prisma_bug_1_id1, prisma_bug_1_id2], references: [id1, id2], map: "prisma_bug_1_b_fk")
}Environment & setup
- OS: macOS m1
- Database: PostgresQL
- Node.js version: v18.16.0
Prisma Version
I reproduced this with both 4.16.1 and 5.9.1. the following is for 4.16.1, but I also tried upgrading to ensure it wasn't fixed by 5.9.1
prisma : 4.16.1
@prisma/client : 4.16.1
Current platform : darwin-arm64
Query Engine (Node-API) : libquery-engine b20ead4d3ab9e78ac112966e242ded703f4a052c (at ../../node_modules/.pnpm/@prisma+engines@4.16.1/node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Migration Engine : migration-engine-cli b20ead4d3ab9e78ac112966e242ded703f4a052c (at ../../node_modules/.pnpm/@prisma+engines@4.16.1/node_modules/@prisma/engines/migration-engine-darwin-arm64)
Format Wasm : @prisma/prisma-fmt-wasm 4.16.0-66.b20ead4d3ab9e78ac112966e242ded703f4a052c
Default Engines Hash : b20ead4d3ab9e78ac112966e242ded703f4a052c
Studio : 0.484.0