Prisma generating drift without schema changes

[GabrielFerrarini]

Bug description

prisma migrate dev incorrectly generates migration without schema changes

How to reproduce

1. Use the schema in schema section with Postgres 14.
2. Run `prisma migrate dev --name "migration 1"
3. Check the migration file

-- CreateTable
CREATE TABLE "Header" (
    "id" TEXT NOT NULL,

    CONSTRAINT "Header_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "Item" (
    "id" TEXT NOT NULL,
    "header_id" TEXT NOT NULL,
    "item_a_header_id" TEXT NOT NULL,
    "item_b_header_id" TEXT NOT NULL,

    CONSTRAINT "Item_pkey" PRIMARY KEY ("id")
);

-- AddForeignKey
ALTER TABLE "Item" ADD CONSTRAINT "a_header_fk" FOREIGN KEY ("header_id") REFERENCES "Header"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "Item" ADD CONSTRAINT "b_header_fk" FOREIGN KEY ("header_id") REFERENCES "Header"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

5. Run `prisma migrate dev --name "migration 2"
6. Check the Drift message shown

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 `Item` table
  [*] Renamed the foreign key "b_header_fk" to "a_header_fk"

7. Confirm the migration and check the output

-- RenameForeignKey
ALTER TABLE "Item" RENAME CONSTRAINT "b_header_fk" TO "a_header_fk";

8. Obviously migration fails

Error: P3018

A migration failed to apply. New migrations cannot be applied before the error is recovered from. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve

Migration name: 20231015115813_migration_2

Database error code: 42710

Database error:
ERROR: constraint "a_header_fk" for relation "Item" already exists

Expected behavior

Either the initial migration is not allowed or the second is not generated.

Prisma information

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator db {
  provider                    = "prisma-client-py"
  interface                   = "asyncio"
  recursive_type_depth        = 5
  enable_experimental_decimal = true
}

model Header {
  id String @id @default(cuid())

  items_a Item[] @relation("item_a")
  items_b Item[] @relation("item_b")
}

model Item {
  id               String @id @default(cuid())
  header_id        String
  item_a_header_id String
  item_b_header_id String

  item_a Header @relation("item_a", fields: [header_id], references: [id], map: "a_header_fk")
  item_b Header @relation("item_b", fields: [header_id], references: [id], map: "b_header_fk")
}

Environment & setup

• OS: Tested on Debian based docker image

# syntax=docker/dockerfile:1.4
FROM --platform=linux/x86_64 python:3.11-slim-bookworm as deps

• Database: Postgres 14
• Python version: 3.11
• Prisma version:

prisma                  : 4.15.0
prisma client python    : 0.10.0
platform                : debian-openssl-3.1.x
expected engine version : 8fbc245156db7124f997f4cecdd8d1219e360944
installed extras        : []
install path            : /home/gabriel/.../.venv/lib/python3.11/site-packages/prisma
binary cache dir        : /home/gabriel/.cache/prisma-python/binaries/4.15.0/8fbc245156db7124f997f4cecdd8d1219e360944