Migration Script Created With Foreign Key Fields in Wrong Order

[miller-productions]

Bug description

I am trying out a schema with a model that has a foreign key relation containing three fields.

Running migrate against this schema results in a generated migration.sql where there is an issue with the foreign key fields being in the wrong order

How to reproduce

1. Run prisma migrate dev --preview-feature
2. See error

Error: Database error: Error querying the database: db error: ERROR: foreign key constraint "Person_partyId_tenant_partyType_fkey" cannot be implemented
DETAIL: Key columns "tenant" and "partyType" are of incompatible types: integer and text.
   0: migration_core::api::ApplyMigrations
             at migration-engine\core\src\api.rs:72

The bug is a result of the following SQL that gets generated.

ALTER TABLE "Person" ADD FOREIGN KEY ("partyId", "tenant", "partyType") REFERENCES "Party"("partyId", "partyType", "tenant") ON DELETE CASCADE ON UPDATE CASCADE;

Notice that the foreign key fields are specified in the wrong order.

If I manually go in there and re-order the foreign key fields to this, it resolves the issue.

ALTER TABLE "Person" ADD FOREIGN KEY ("partyId", "partyType", "tenant") REFERENCES "Party"("partyId", "partyType", "tenant") ON DELETE CASCADE ON UPDATE CASCADE;

Expected behavior

Migrate should complete successfully.

Prisma information

generator client {
  provider = "prisma-client-js"
  previewFeatures = ["createMany"]
}

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

model Party {
  partyId      Int            @default(autoincrement())
  tenant       Int
  partyType    String
  Tenant       Tenant         @relation(fields: [tenant], references: [tennantId])
  Person       Person?

  @@id([partyId, tenant])
  @@unique([partyId, partyType, tenant], name: "Party_partyId_partyType_tenant_key")
  @@index([tenant], name: "fki_Party_Tenant_tenantId_fkey")
}

model Person {
  partyId   Int
  firstname String
  lastname  String?
  tenant    Int
  partyType String
  Party     Party   @relation(fields: [partyId, partyType, tenant], references: [partyId, partyType, tenant])
  Tenant    Tenant  @relation(fields: [tenant], references: [tennantId])

  @@id([partyId, tenant])
  @@unique([partyId, partyType, tenant], name: "Person_partyId_partyType_tenant_key")
  @@index([partyId, partyType, tenant], name: "fki_Person_Party_partyId_partyType_tenant_fkey")
  @@index([tenant], name: "fki_Person_Tenant_id_fkey")
}

model Tenant {
  name                 String
  tennantId            Int                    @id @default(autoincrement())
  Party                Party[]
  Person               Person[]
}

Environment & setup

• OS: Windows
• Database: PostgreSQL
• Node.js version: 14.14.0
• Prisma version:

Environment variables loaded from ..\.env
Environment variables loaded from prisma\.env
prisma               : 2.16.1
@prisma/client       : 2.16.1
Current platform     : windows
Query Engine         : query-engine 8b74ad57aaf2cc6c155f382a18a8e3ba95aceb03 (at ..\node_modules\@prisma\engines\query-engine-windows.exe)
Migration Engine     : migration-engine-cli 8b74ad57aaf2cc6c155f382a18a8e3ba95aceb03 (at ..\node_modules\@prisma\engines\migration-engine-windows.exe)
Introspection Engine : introspection-core 8b74ad57aaf2cc6c155f382a18a8e3ba95aceb03 (at ..\node_modules\@prisma\engines\introspection-engine-windows.exe)
Format Binary        : prisma-fmt 8b74ad57aaf2cc6c155f382a18a8e3ba95aceb03 (at ..\node_modules\@prisma\engines\prisma-fmt-windows.exe)
Studio               : 0.346.0
Preview Features     : createMany

[tomhoule]

That looks like a legit bug — I flagged this so we can reproduce the issue, and fix it soon. Thanks for reporting!

[pimeys]

Checking this out today.

[pimeys]

Yup, can reproduce.

[pimeys]

Fix in 2.18.0.