Cannot add foreign key constraint for MySQL migration that tries to create foreign key pointing to non-PK and unindexed field

[janpio]

This schema:

generator client {
  provider = "prisma-client-js"
}

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

model A {
  id         Int          @id @default(autoincrement())
  custom_id  String
  B          B[]
}

model B {
  id        Int     @id @default(autoincrement())
  a_id String
  A         A @relation(fields: [a_id], references: [custom_id])
}

(Note how custom_id is not indexed at all [and also not unique, which might be invalid, see issue https://github.com//issues/13340)

leads to this migration SQL:

-- CreateTable
CREATE TABLE `A` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `custom_id` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `B` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `a_id` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `B` ADD CONSTRAINT `B_a_id_fkey` FOREIGN KEY (`a_id`) REFERENCES `A`(`custom_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

Which on execution fails with:

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: 20220512183128_init

Database error code: 1215

Database error:
Cannot add foreign key constraint

Please check the query number 3 from the migration file.

Adding an index @@index([custom_id]) to A makes the migration pass.

[janpio]

This issue might become obsolete if #13340 is fixed by changing the validation.