prisma: Remove @unique from id relationship does not remove the unique index.

Bug description

I have a relationship between to Models. They and linked through a id. Initial I had set up that ID to be unique but the logic changed so I removed it. However, it does not remove the unique index from the MySQL table and still fails on that constraint. I had to manually remove it.

How to reproduce

Create a model like the below.

model Envelope {
    ClaimedCoupon    ClaimedCoupon?
}

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

Then change the model to this.

model Envelope {
    ClaimedCoupon    ClaimedCoupon[]
}

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

Also tried:

model Envelope {
    ClaimedCoupon    ClaimedCoupon[]
}

model ClaimedCoupon {
    Envelope      Envelope  @relation(fields: [envelopeId], references: [id])
    envelopeId
    
    @@index([envelopeId], name: "envelopeId")
}

Run create migration.
No change happens.
The unique index remains.

Expected behavior

The unique index should get deleted.

Environment & setup

OS: MacOS and Ubuntu 18
Database: MySQL
Node.js version: 15.3.0
Prisma version: ^2.14.0

About this issue

Original URL
State: closed
Created 3 years ago
Reactions: 1
Comments: 16 (7 by maintainers)

Most upvoted comments

OK, I re-read and did some testing again @ssmith-14Four

This is what I think is happening:

In the first schema you share you have a 1:1 relation: there is a relation scalar (envelopeId) and on the *opposite side there is an optional back-relation ClaimedCoupon? See docs
In this case, Prisma will create a unique index, even if none is defined in the schema. But it happens that you also have @unique in there. If you omit it, the resulting DB schema is the same.
Then you change it to a 1:n relation, by removing the @unique attribute from the foreign key envelopeId and making the back-relation ClaimedCoupon[] an array. At this point you would expect for the index to be dropped, but it seems you are hitting what looks like a bug that is unrelated to your previous relation syntax.

I just created another issue for that here. I will add that for the 1:1 relation, prisma already adds a uniqueness constraint anyway so it’s not necessary. You can omit the @unique in your first schema and the result there is the same.

What happens is when you transition to the second schema you want to remove your explicit @unique and this is not happening.

Does this make sense to you?

albertoperdomo on Feb 1, 2021

@martineboh this doesn’t help as it just drops and recreates the tables but not the indexes. Also I’m not looking for a fix as I’ve already fixed it. I’m reporting a bug that should not require any of these things as it only requires the unique index to be deleted.

Also having to delete all the data from your database to remove a unique index would make Prisma not a viable library for a production application. This is not a solution to the bug, it’s a workaround.

A better workaround which doesn’t destroy data is to just manually delete the index instead. No data loss.

mjoynes-14four on Jan 31, 2021

Ah, this index is there because you have a 1:1 relation, ti’s automatically inserted.

tomhoule on Feb 1, 2021

@albertoperdomo Yup that makes sense. Thank you for looking into this.

mjoynes-wombat-web on Feb 2, 2021