Foreign key indexes are not well changed by prisma

[bigTeethLi]

Bug description

If I initially create a 1:1 relation and then need to change it to a many-to-many relation, the foreign key index does not change from unique to non-unique. And need to drop the table and then re-create, the foreign key index can be updated.

How to reproduce

1. Create a 1:1 relation
2. Run prisma generate && prisma db push
3. Change the 1:1 relation to Many-to-Many relation
4. Run prisma generate && prisma db push
5. See error, the foreign key is still a unique index
   
6. Then I drop the table and run prisma generate && prisma db push, the foreign key can only be changed to non-unique indexes
   

Expected behavior

No response

Prisma information

1:1 relation(use Official document example)

model Post {
  id         Int                 @id @default(autoincrement())
  title      String
  categories CategoriesOnPosts?
}

model Category {
  id    Int                 @id @default(autoincrement())
  name  String
  posts CategoriesOnPosts?
}

model CategoriesOnPosts {
  post       Post     @relation(fields: [postId], references: [id])
  postId     Int // relation scalar field (used in the `@relation` attribute above)
  category   Category @relation(fields: [categoryId], references: [id])
  categoryId Int // relation scalar field (used in the `@relation` attribute above)
  assignedAt DateTime @default(now())
  assignedBy String

  @@id([postId, categoryId])
}

Then change to Many-to-Many relation(only "?" to "[]"):

model Post {
  id         Int                 @id @default(autoincrement())
  title      String
  categories CategoriesOnPosts[]
}

model Category {
  id    Int                 @id @default(autoincrement())
  name  String
  posts CategoriesOnPosts[]
}

model CategoriesOnPosts {
  post       Post     @relation(fields: [postId], references: [id])
  postId     Int // relation scalar field (used in the `@relation` attribute above)
  category   Category @relation(fields: [categoryId], references: [id])
  categoryId Int // relation scalar field (used in the `@relation` attribute above)
  assignedAt DateTime @default(now())
  assignedBy String

  @@id([postId, categoryId])
}

Environment & setup

• OS: MAC OS
• Database: MySQL
• Node.js version: v16.13.2

Prisma Version

prisma: "3.9.1"

[Alverrt]

I have the similar issue. Here is my model.

Then I type npx prisma db push. And cli says everything is fine. Then I type npx prisma db pull. The @unique attribute keep coming back.

[janpio]

Just to confirm: Are you also on MySQL @Alverrt?

[janpio]

I can not reproduce your simplified case @Alverrt:

I start with this schema:

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

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

model Foo {
  id  Int @id @default(autoincrement())
  bar Int @unique
}

Migrate via db push, and see that indeed a unique key was created.

Then I remove the @unique for this schema:

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

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

model Foo {
  id  Int @id @default(autoincrement())
  bar Int
}

Running db push indeed does remove the unique constraint, and running db pull again does not change the schema.

Adding another field and then an unique that covers both fields, does not change the outcome:

model Foo {
  id  Int @id @default(autoincrement())
  baz Int
  bar Int @unique

  @@unique([baz, bar])
}

I suggest you create a new issue @Alverrt so we can investigate this better. Thanks!

[janpio]

Hey @bigTeethLi, I tested this with Prisma 3.12.0 and unfortunately could not reproduce:

I started with this schema file:

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

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

model Post {
  id         Int                @id @default(autoincrement())
  title      String
  categories CategoriesOnPosts?
}

model Category {
  id    Int                @id @default(autoincrement())
  name  String
  posts CategoriesOnPosts?
}

model CategoriesOnPosts {
  post       Post     @relation(fields: [postId], references: [id])
  postId     Int // relation scalar field (used in the `@relation` attribute above)
  category   Category @relation(fields: [categoryId], references: [id])
  categoryId Int // relation scalar field (used in the `@relation` attribute above)
  assignedAt DateTime @default(now())
  assignedBy String

  @@id([postId, categoryId])
}

When formatting the Prisma schema file, Prisma automatically added the @unique explicitly to the two fields:

model CategoriesOnPosts {
  post       Post     @relation(fields: [postId], references: [id])
  postId     Int      @unique // relation scalar field (used in the `@relation` attribute above)
  category   Category @relation(fields: [categoryId], references: [id])
  categoryId Int      @unique // relation scalar field (used in the `@relation` attribute above)
  assignedAt DateTime @default(now())
  assignedBy String

  @@id([postId, categoryId])
}

Then I ran db push and got 2 unique constraints created:

CREATE TABLE `CategoriesOnPosts` (
  `postId` int NOT NULL,
  `categoryId` int NOT NULL,
  `assignedAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `assignedBy` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`postId`,`categoryId`),
  UNIQUE KEY `CategoriesOnPosts_postId_key` (`postId`),
  UNIQUE KEY `CategoriesOnPosts_categoryId_key` (`categoryId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Then I changed the models:

model Post {
  id         Int                 @id @default(autoincrement())
  title      String
  categories CategoriesOnPosts[]
}

model Category {
  id    Int                 @id @default(autoincrement())
  name  String
  posts CategoriesOnPosts[]
}

model CategoriesOnPosts {
  post       Post     @relation(fields: [postId], references: [id])
  postId     Int // relation scalar field (used in the `@relation` attribute above)
  category   Category @relation(fields: [categoryId], references: [id])
  categoryId Int // relation scalar field (used in the `@relation` attribute above)
  assignedAt DateTime @default(now())
  assignedBy String

  @@id([postId, categoryId])
}

(This time formatting did not add any @unique, as it should not).

Then I ran db push and the two constraints were removed in the database as well:

CREATE TABLE `CategoriesOnPosts` (
  `postId` int NOT NULL,
  `categoryId` int NOT NULL,
  `assignedAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `assignedBy` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`postId`,`categoryId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Can you maybe try to reproduce your problem again with the newest Prisma version?

[janpio]

Ping @Alverrt + @bigTeethLi - is this still a problem? Or did it go away?

[janpio]

Hey, last ping @Alverrt and @bigTeethLi - we would love to fix this bug if it is real, but with no further information we have no way of doign that. We would really love if you could help us here. Otherwise we will have to close this issue until someone reports it again 😢

[Jolg42]

We are closing this but we're happy to reopen if you have more information.