Incorrect migration creates on sql server when index deleted

[ingbond]

Bug description

Initial prisma model contains unique keys:

model Logbook {    
  categoryId     String?         @db.UniqueIdentifier
  date           DateTime       @db.Date()
  @@unique([date, categoryId], name: "Date_Category_unique_constraint")
}

It creates such sql statement:
-- CreateIndex CREATE UNIQUE NONCLUSTERED INDEX [Logbook_date_categoryId_key] ON [dbo].[Logbook]([date], [categoryId]);
When I delete unique constraint from prisma's schema model

model Logbook {    
  categoryId     String?         @db.UniqueIdentifier
  date           DateTime       @db.Date()
}

it creates migration with such statement:
-- DropIndex ALTER TABLE [dbo].[Logbook] DROP CONSTRAINT [Logbook_date_categoryId_key];
Applying this migration throws an error:
"Database error:
'Logbook_date_categoryId_key' is not a constraint."

How to reproduce

1. Add unique keys constraint for your model
2. Apply migration
3. Delete constraint
4. Apply migration

Expected behavior

Should call DROP INDEX, not DROP CONSTRAINT;

Prisma information

model Logbook {    
  categoryId     String?         @db.UniqueIdentifier
  date           DateTime       @db.Date()
  @@unique([date, categoryId], name: "Date_Category_unique_constraint")
}

Environment & setup

• OS: Windows
• Database: Sql Server
• Node.js version: 16.15.1

Prisma Version

3.15.1

[pimeys]

I cannot reproduce this. Running with SQL Server 2019.

First datamodel:

datasource db {
  provider = "sqlserver"
  url      = "sqlserver://localhost:1433;database=master;user=SA;password=<YourStrong@Passw0rd>;trustServerCertificate=true"
}

model Logbook {
  id         Int      @id
  categoryId String?  @db.UniqueIdentifier
  date       DateTime @db.Date()

  @@unique([date, categoryId], name: "Date_Category_unique_constraint")
}

This is pushed to the database correctly. Then removing the unique:

datasource db {
  provider = "sqlserver"
  url      = "sqlserver://localhost:1433;database=master;user=SA;password=<YourStrong@Passw0rd>;trustServerCertificate=true"
}

model Logbook {
  id         Int      @id
  categoryId String?  @db.UniqueIdentifier
  date       DateTime @db.Date()
}

This creates a migration:

BEGIN TRY

BEGIN TRAN;

-- DropIndex
ALTER TABLE [dbo].[Logbook] DROP CONSTRAINT [Logbook_date_categoryId_key];

COMMIT TRAN;

END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
BEGIN
    ROLLBACK TRAN;
END;
THROW

END CATCH

Now I can execute this and the constraint is removed.

[pimeys]

Ah, found it! So you must first create a table without a unique constraint, then add it and then remove it again. Now it fails.

[pimeys]

I made a fix, this should work with all Prisma cases. Read the details from the PR above.

Now you should manually edit the drop to your migration for it to work. In the future, if you alter a table with @unique, we add a constraint instead of an index. This can then be dropped with DROP CONSTRAINT.

Definitely interesting how SQL Server makes a distinction here, and why you can't drop a unique index with DROP CONSTRAINT.

[pimeys]

Also it's a bit problematic for existing indices, if coming to a brownfield project with Prisma. We do not make any distinction between unique index or unique constraint...

[pimeys]

After sleeping one night on this, I decided to detect is it an index or unique, and drop accordingly to fix the brownfield problem. Out in about three weeks in 4.1.0.

[pimeys]

What can still break is if your development database and production database are not in sync. You drop a constraint in dev, and it fails in production because there it's actually an index. It's kind of not very probable that this happens, but if it gets too common we might need to consider to have the information in the Prisma schema language. E.g. @unique(constraint: true). But I'd prefer not to have that if possible.