Removing a model with a foreign key fails

matthewmueller commented 3 years ago

Bug description

Trying to remove a model with a foreign key on a database without data, lands me in a permanently broken state on SQL Server.

Error: Database error: Error querying the database: ''FK__Post__userId__267ABA7A' is not a constraint.' on server 369707b4e132 executing  on line 8 (code: 3728, state: 1, class: 16)
   0: migration_core::api::ApplyMigrations
             at migration-engine/core/src/api.rs:102

How to reproduce

Previous

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

datasource ms {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
}

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

model Post {
  id     Int    @id @default(autoincrement())
  title  String
  User   User   @relation(fields: [userId], references: [id])
  userId Int
}

Current

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

datasource ms {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
}

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

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

npx prisma migrate dev --preview-feature
Environment variables loaded from prisma/.env
Prisma schema loaded from prisma/schema.prisma
Datasource "ms"

✔ Name of migration … 

Error: Database error: Error querying the database: ''FK__Post__userId__267ABA7A' is not a constraint.' on server 417d5b86856f executing  on line 8 (code: 3728, state: 1, class: 16)
   0: migration_core::api::ApplyMigrations
             at migration-engine/core/src/api.rs:102

Puts me in a permanently broken state. I need to manually adjust my SQL scripts to remove the migration.

Expected behavior

This should work as expected.

Environment & setup

OS:
Database:
Node.js version:

Prisma version:

Environment variables loaded from prisma/.env
@prisma/cli          : 2.13.0-dev.100
@prisma/client       : 2.13.0-dev.100
Current platform     : darwin
Query Engine         : query-engine 833ab05d2a20e822f6736a39a27de4fc8f6b3e49 (at node_modules/@prisma/engines/query-engine-darwin)
Migration Engine     : migration-engine-cli 833ab05d2a20e822f6736a39a27de4fc8f6b3e49 (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine : introspection-core 833ab05d2a20e822f6736a39a27de4fc8f6b3e49 (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary        : prisma-fmt 833ab05d2a20e822f6736a39a27de4fc8f6b3e49 (at node_modules/@prisma/engines/prisma-fmt-darwin)
Studio               : 0.329.0
Preview Features     : microsoftSqlServer

pimeys commented 3 years ago

Also removing a default value fails:

model User {
  id   Int     @id @default(autoincrement())
  name String  @default("Musti")
}

to

model User {
  id   Int     @id @default(autoincrement())
  name String?
}

gives us an error

Caused by:
    0: Database error: Error querying the database: ''DF__User__name__36B12243' is not a constraint.' on server 8c638ed057de executing  on line 2 (code: 3728, state: 1, class: 16)

albertoperdomo commented 3 years ago

Keeping the candidate label so that you can discuss it in your planning @pimeys @matthewmueller - for us to make sense to tackle it this sprint.

pimeys commented 3 years ago

@albertoperdomo it's already been fixed in a PR. We just need to talk a bit more about possible issues with the shadow database, and randomized constraint names. Hopefully there will be no other problems than these two.

prisma / migrate