removing @unique in schema.prisma does not generate SQL to do so

[wilsonrocks]

This occurs for me using MySQL 5.7

To reproduce:

1. Make a model for a new table with a field that is marked as @unique
2. Generate and run migrations.
3. Remove the @unique
4. Generate migrations
5. There is no ALTER TABLE foo DROP KEY 'table_name_field_name_key; generated in the migrations

This is a problem for me, partly because I need that field not to be unique, but also because the generated prisma client thinks that everything is fine - so the typescript allows statements that then fail due to a unique key violation.

[janpio]

Note: This might be influenced by the column also being a foreign key.
(Can you confirm @wilsonrocks? Can you maybe share the full schema?)

[jkomyno]

Ok, I'm now able to reproduce this issue, which seems to happen whenever unique constraints are applied to foreign keys.
Reproduction steps:

• Create a docker-compose.yml file with the following content:

# docker-compose.yml
version: '3.7'

services:
  mysql:
    image: mysql:5.7
    command: --default-authentication-plugin=mysql_native_password --skip-grant-tables
    restart: always
    environment:
      - MYSQL_ROOT_PASSWORD=root
      - MYSQL_DATABASE=tests
      - MYSQL_USER=prisma
    ports:
      - '3306:3306'

• Spin a mysql server up with docker-compose up

In a different terminal:

• Run mkdir reproduce-12732 && cd reproduce-12732
• Run npx prisma init --datasource-provider mysql
• Write DATABASE_URL="mysql://root:root@localhost:3306/tests" to the autogenerated .env file
• Replace the prisma/schema.prisma file with:

// prisma/schema.prisma

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

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

// Data model
model Post {
  id       Int    @id @default(autoincrement())
  title    String
  author   User?  @relation(fields: [authorId], references: [id])
  authorId Int?   @unique
}

model User {
  id    Int     @id @default(autoincrement())
  name  String?
  posts Post[]
}

• Run a first migration with prisma migrate dev --name init. This would generate a migration file similar to the following:

-- prisma/migrations/20220429092455_init/migration.sql

-- CreateTable
CREATE TABLE `Post` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(191) NOT NULL,
    `authorId` INTEGER NULL,

    UNIQUE INDEX `Post_authorId_key`(`authorId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `User` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(191) NULL,

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

-- AddForeignKey
ALTER TABLE `Post` ADD CONSTRAINT `Post_authorId_fkey` FOREIGN KEY (`authorId`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

• Remove the @unique constraint, replacing the prisma/schema.prisma file with:

// prisma/schema.prisma

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

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

// Data model
model Post {
  id       Int    @id @default(autoincrement())
  title    String
  author   User?  @relation(fields: [authorId], references: [id])
  authorId Int?
}

model User {
  id    Int     @id @default(autoincrement())
  name  String?
  posts Post[]
}

• Run a second migration with prisma migrate dev --name drop-unique. This doesn't generate any migration file. Instead, we obtain the following output:

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": MySQL database "tests" at "localhost:3306"

Already in sync, no schema change or pending migration was found.

✔ Generated Prisma Client (3.13.0-integration-fix-db-pull-url-overwrites-provider-cockroachdb.8 | library) to ./../node_modules/@prisma/clie
nt in 94ms

If you now open another terminal and create new users with duplicated email, you can see that the unique constraint still exists.

• connect to the running mysql instance with docker-compose exec mysql sh
• # mysql tests
• List the existing tables with mysql> SHOW TABLES;, which should yield the following:

+--------------------+
| Tables_in_tests    |
+--------------------+
| Post               |
| User               |
| _prisma_migrations |
+--------------------+
3 rows in set (0.00 sec)

• Run the following statements, which create a user and try to create two posts with the same author:

mysql> INSERT INTO `User` (`name`) VALUES ('foo');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `Post` (`title`, `authorId`) VALUES ('title-1', 1);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `Post` (`title`, `authorId`) VALUES ('title-2', 1);
ERROR 1062 (23000): Duplicate entry '1' for key 'Post_authorId_key'

[Rodrigodd]

Is there a workaround for this bug?

[janpio]

@Rodrigodd One the second migrate dev run you could use migrate dev --create-only instead and manually add the SQL to drop the unique index. Then apply it with migrate deploy and your database should match the state of your Prisma schema going forward.

[RNKushwaha]

That's weird

[Andndre]

Any Updates??