Removing @unique from 1:n relation scalar field not picked up by Migrate

[albertoperdomo]

Steps

1. With the following schema, generate a migration

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

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

model Envelope {
  id            Int            @id @default(autoincrement())
  ClaimedCoupon ClaimedCoupon[]
}

model ClaimedCoupon {
  id         Int      @id @default(autoincrement())
  envelopeId Int      @unique
  Envelope   Envelope @relation(fields: [envelopeId], references: [id])
}

-- CreateTable
CREATE TABLE `Envelope` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,

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

-- CreateTable
CREATE TABLE `ClaimedCoupon` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `envelopeId` INTEGER NOT NULL,
UNIQUE INDEX `ClaimedCoupon.envelopeId_unique`(`envelopeId`),

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

-- AddForeignKey
ALTER TABLE `ClaimedCoupon` ADD FOREIGN KEY (`envelopeId`) REFERENCES `Envelope`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

2. Remove @unique from field envelopeId and run prisma migrate dev. Prisma Migrate will claim the database is in sync and not drop the index.

Notes

In general, this is a bit of an edge case, because adding the uniqueness constraint, turns the 1:n relation into a 1:1 relation. Usually, developers would utilize our specific 1:1 syntax for this.

It does impact potentially developers who may be transitioning from a 1:1 to a 1:n relation. Surely if they have defined the uniqueness constraint manually (Prisma already creates such an index under the hood).

[tomhoule]

I have a test reproduction for this problem. It is MySQL-specific.

[pantharshit00]

This is still reproducible in 2.26.0-dev.46

[janpio]

Note: We could add a relation validation to make clear that this is actually not a 1:n but a 1:1.

[twofingerrightclick]

I am encountering a similar issue from the standpoint of prisma migrate dev adding a unique constraint to a column even though the column has not previously had a @unique constraint. That is, the standing migration "A" and schema has a model with a column that has relation of 1:1, but when I run prisma migrate dev "B" to update the db it attempts to add a unique constraint to the column, but the model wasn't changed between "A" and "B".

The project I am working on currently is using:

• prisma@2.30.0
• "@prisma/engines": "2.30.0-28.60b19f4a1de4fe95741da371b4c44a92f4d1adcb"
• postgresql db

Also the constraints are added with the engines for Windows and Ubuntu (tried Ubuntu to see if any luck), and also with the final version of Prisma 2 - prisma@2.30.3 .

Other team members have encountered this issue a handful of times.

Work around:
I ended up making use of the --create-only flag like so: prisma migrate dev --create-only. Then modify the generated migration to remove the sql commands that would do the undesired unique constraints. Then deployed the migration free of the undesired constraints.

[tomhoule]

Hi @twofingerrightclick, thanks for the report. Would you be ok sharing your a reproduction? Also, are you sure this is a 1:n relation and not a 1:1 relation? (1:n would be model B { a A } model A { b B[] }, 1:1 would be model B { a A } model A { b B? }) Aro you on MySQL? The more context you give us, the more we'll be able to help, but my impression right now is that you have a different problem from what this issue is originally about.

[twofingerrightclick]

@tomhoule not 1:n, but 1:1 🤦‍♂️ thank you for pointing that out, so perhaps the wrong issue to address it. I will see if I can find any other issues with @unique being applied automatically when not desired.

[tomhoule]

@twofingerrightclick There's an issue about this: #10503 — it's the only case where Prisma implicitly adds indexes/constraints even if they're not in your schema, and I'd like to get rid of it, but it will be a breaking change (so it's going to be for Prisma 4 at the earliest).

[BEDIRHANSAMSA]

any ETA on this? @janpio

[janpio]

We are currently cleaning up the 1:1 case (#10503), afterwards Prisma should be consistent how we deal with @unique and can also revisit this properly.

[tomhoule]

@janpio I am pretty sure this is not related to any of the relation uniqueness issues we have dealt with recently.

[celso-alexandre]

Is there any news regarding it?

[petter-kaspersen]

@janpio Any updates on this? I am able to reproduce with a relation that used to be one-to-one with a unique index on foreignIdHere - then converted to a one-to-many with the @ unique removed. MySQL 8.