Migrate: One-to-Many self relations creates a Unique index

[pguilbault]

Bug description

As discussed here, prisma migrate save creates a unique index for one-to-many self relations.

How to reproduce

My datamodel with the one-to-many self relation :

model Location {
  id        String      @id @default(cuid())
  parent    Location?   @relation("LocationToLocation_parent", fields:[parentId], references: [id])
  parentId  String?     @map("parent")
  children  Location[]  @relation("LocationToLocation_parent")
}

Here's the migration's Readme after running the prisma migrate save --experimental command :

CREATE UNIQUE INDEX "Location_parent" ON "Location"("parent")

Which throws an error when doing prisma migrate up because I already have many rows linked to the same parent.

Expected behavior

A non-unique index creation:

CREATE INDEX "Location_parent" ON "Location"("parent")

Prisma information

@prisma/cli          : 2.0.0-beta.1
Current platform     : debian-openssl-1.1.x
Query Engine         : prisma 2accb9c7eacdc984874eaeb63377fe705dfd3203 (at /home/pigui/work/yuno/yuno/node_modules/@prisma/cli/query-engine-debian-openssl-1.1.x)
Migration Engine     : migration-engine-cli 2accb9c7eacdc984874eaeb63377fe705dfd3203 (at /home/pigui/work/yuno/yuno/node_modules/@prisma/cli/migration-engine-debian-openssl-1.1.x)
Introspection Engine : introspection-core 2accb9c7eacdc984874eaeb63377fe705dfd3203 (at /home/pigui/work/yuno/yuno/node_modules/@prisma/cli/introspection-engine-debian-openssl-1.1.x)

Environment & setup

• OS: Ubuntu 18.04 LTS
• Database: PostgreSQL 10.3 (docker)
• Prisma version: 2.0.0-beta.1
• Node.js version: v10.14.2

[tylerjbainbridge]

@pguilbault Did you find a temporary workaround for this in the meantime?

[nickreynke]

@pguilbault Did you find a temporary workaround for this in the meantime?

Drop the UNIQUE INDEX (if you have no rows in your table as @pguilbault stated). For the above example:

DROP INDEX "Location_parent";
CREATE INDEX "Location_parent" ON "Location"("parent");

[pguilbault]

@pguilbault Did you find a temporary workaround for this in the meantime?

Not really because I already have rows in the database (brownfield project migration).
The migrate command fails when creating the unique index. I let it in a pending state and execute manually the subsequent statements (through pgAdmin or psql for example).

I you have no row in your table, you can apply the commands given by @nickreynke.

[ranjan-purbey]

@tomhoule any updates on this?

[tomhoule]

@ranjan-purbey it was the weekend so I wasn't working :) Now it's monday morning, and I have a PR to fix this https://github.com/prisma/prisma-engines/pull/651 - I will ping this issue when it is reviewed, merged and on its way to alpha.

[ranjan-purbey]

Thanks @tomhoule for the quick update and thanks a ton for taking up the issue

[tomhoule]

The PR is merged, the fix will land in the next alpha (and then beta) release. Reminder about indexes for relations: mysql automatically creates indexes on foreign key columns, but postgres and sqlite do not, and we do not change that (we may, in the future). So if you want an index on top of the foreign key on postgres/sqlite, you have to use @index or @@index.

[pguilbault]

It works perfectly with the latest alpha release.

Thanks for the fix @tomhoule, I close this issue !