P
Prisma•7mo ago
XenTobias

How to Handle Partial Unique Indexes with Prisma Migrations?

Hey Community, I'm working on a use case where I need to manually modify a Prisma migration file to add a WHERE clause to a unique index. For example: 
CREATE UNIQUE INDEX "workflow_releases_active_workflow_id_key" 
ON "workflow_releases"("active", "workflow_id") 
WHERE "active" = true;
CREATE UNIQUE INDEX "workflow_releases_active_workflow_id_key" 
ON "workflow_releases"("active", "workflow_id") 
WHERE "active" = true;
The issue is that Prisma doesn't recognize this line afterwards. When I run prisma migrate dev, it tries to add the unique constraint again: 
⚠️  Warnings for the current datasource:

  • A unique constraint covering the columns `[active, workflow_id]` on the table `workflow_releases` will be added. If there are existing duplicate values, this will fail.
⚠️  Warnings for the current datasource:

  • A unique constraint covering the columns `[active, workflow_id]` on the table `workflow_releases` will be added. If there are existing duplicate values, this will fail.
Is there any known workaround to prevent Prisma from re-adding this constraint? Thanks in advance!
3 Replies
Prisma AI Help
Prisma AI Help•7mo ago
You decided to hold for human wisdom. We'll chime in soon! Meanwhile, #ask-ai is there if you need a quick second opinion.
Nurul
Nurul•7mo ago
Hey! It seems that you are running into this issue: https://github.com/prisma/prisma/issues/13417
GitHub
Partial unique indexes are being recreated on every migration with ...
Bug description We are using partial unique indexes, since we are implementing soft delete-adding deletedAt column to our models. Since we want the columns to still behave as unique, we mark them w...
Nurul
Nurul•7mo ago
As a workaround, you can create an empty migration file and add the SQL to create a partial unique index in this migration https://github.com/prisma/prisma/issues/6974#issuecomment-2489730870
GitHub
Conditional uniqueness constraints; Partial/Filtered (Unique) Index...
Problem I would like to be able to define a uniqueness constraint that has conditional elements to it so that it can map to and align with a conditional unique index/constraint within the database....

Did you find this page helpful?