Conditional uniqueness constraints; Partial/Filtered (Unique) Indexes

[ArthurThornton]

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.

Suggested solution

Modify the @@unique modifier to include a where: { } argument that could define the condition that allows this to be unique.

[janpio]

How would this be modelled on a database level?

[ArthurThornton]

One example of how this might be used would be a model of phone numbers for a user, where we have a field to determine which one is the primary number:

model PhoneNumber {
  (standard id/created/updated fields)

  userId    String  @map("user_id")
  user      User    @relation(fields: [userId], references: [id])
  number    String
  isPrimary Boolean @map("is_primary")

  @@unique([userId, isPrimary], name: "uniquePrimaryForUser", where: { isPrimary: true })
  @@unique([userId, number], name: "uniqueNumberForUser")
}

In postgres, they support unique indexes with conditions. For this example, you'd create the index with:

CREATE UNIQUE INDEX phone_number_unique_primary_for_user ON "PhoneNumber"(user_id, is_primary) WHERE (is_primary = true);

This is possible within Rails' ActiveRecord validations, which I've used in the past, and something I would derive great value from also getting within Prisma.

[Mando75]

Would love this feature as well.

I think another important reason to support this in Prisma is the fact that from what I can tell, Prisma ignores conditional unique constraints upon introspection. Example: An existing db with a single table created using the following (Postgres) SQL commands:

CREATE TABLE schemas(
  id SERIAL,
  name TEXT NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT false,
  schema_key UUID NOT NULL DEFAULT gen_random_uuid(),
  schema_version INT NOT NULL DEFAULT 1,
  schema_definition TEXT NOT NULL,
  PRIMARY KEY ("id")
);

CREATE INDEX schema_key_index ON schemas("schema_key");
CREATE UNIQUE INDEX single_active_schema ON schemas("is_active", "schema_key") WHERE is_active IS TRUE;

I would expect running prisma introspect and prisma migrate dev --name init_db --create-only to initialize the application would include both the index and the unique constraint defined on the table. Instead, it only captures the index on schema_key

prisma migrate output:

-- CreateTable
CREATE TABLE "schemas" (
    "id" SERIAL NOT NULL,
    "name" TEXT NOT NULL,
    "is_active" BOOLEAN NOT NULL DEFAULT false,
    "schema_key" UUID NOT NULL DEFAULT gen_random_uuid(),
    "schema_version" INTEGER NOT NULL DEFAULT 1,
    "schema_definition" TEXT NOT NULL,

    PRIMARY KEY ("id")
);

-- CreateIndex
CREATE INDEX "schema_key_index" ON "schemas"("schema_key");

schema.prisma

model schemas {
  id                Int     @id @default(autoincrement())
  name              String
  is_active         Boolean @default(false)
  schema_key        String  @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  schema_version    Int     @default(1)
  schema_definition String

  @@index([schema_key], name: "schema_key_index")
}

Obviously this is going to lead to inconsistencies in your database schema if you ever need to rebuild it from prisma migrations. If your application is built assuming those database-level constraints exist, this could cause major consistency and validation problems if they are not included in the migration flow.

[janpio]

I think another important reason to support this in Prisma is the fact that from what I can tell, Prisma ignores conditional unique constraints upon introspection.

Yes, that is currently the case (as we do not support this feature yet) and would probably be solved when this feature request is implemented and this issue is closed.

[brunocasado]

Hey guys,

I faced the same problem yesterday. i needed create two indexes like below:

create unique index sessions_name_product_id on sessions (name, (parent_id is null), product_id) where parent_id is null;
create unique index sessions_name_parent_id_product_id on sessions (name, parent_id, product_id);

Pretty much this is common.

Propose:

  @@unique([fieldA, fieldB, fieldC], name: "a_nice_index_group_name_1", where: { fieldB: null})
  @@unique([fieldA, fieldB, fieldC], name: "a_nice_index_group_name_2")

What do u think guys?

PS: i achieved that upgrading 2.12.1 to recent version, and creating a manual migration.

[azai91]

Any update on this issue?

[Maritt]

Is there a way to upvote this feature? is it currently on any roadmap?

[logesh-kumar]

I am trying to implement soft delete feature. I am facing the similar issue?

Requirement is to add unique key only if deletedAt column is null

[vthang95]

Any update on this issue? Very surprised that this is common but unimplemented yet.

This proposal is okay:

@@unique([slug, isPublished], name: "uniquePublishedPostSlug", where: { isPublished: true })

or

@@unique([slug, isPublished], name: "uniquePublishedPostSlug", where: "isPublished = true")

[hyunwoona]

Please add the support for this feature!!

[KhanhHH]

Any update? This feature is really necessary to implement soft delete.

[steebe]

+1 to the updoot button on this. It's another interesting case where you can create this via raw DDL changes and get what you need thru introspection, but not vice versa... 🤷

[julia-dizhak]

+1 I had the same problem and can't create a compound conditional index for non-deleted projects. Currently, such a feature isn't supported by Prisma and could lead to the continuous creation of new migrations.

Surprised that is not implement, as it quite common case

model Project {
  name                  String              @db.VarChar(255)
  branchId            String?             @map("branch_id") @db.Uuid

  @@unique(fields: [name, branchId], name: "unique_project_name_per_branch", where: { deleted_at: null })
}

[moschiel]

I am having the same issue when using soft-delete.

I need to be able to identify uniquenes for non-deleted data like this.

@@unique(["fieldA", "fieldB"], where: { isDeleted: false } )

[earthpyy]

Really need this!

[issackr]

+1

[zkSoju]

Very common use case for this!

[NathanFrank285]

+1 for this feature, would be a great addition :)

[Doccrazy]

My solution was to manually create the conditional index on DB level using a migration script, and remove the @@unique declaration from the Prisma schema. As a conditional index is ignored by Prisma introspection, there is no conflict with the standard migration behavior.

I added a comment to the schema file to notify developers on what is going on here.

Migration script:

CREATE UNIQUE INDEX "ProjectMember_projectId_userId_key" ON public."ProjectMember" USING btree ("projectId", "userId") WHERE status != 'DELETED';

Still would love to see this implemented properly.

[janpio]

Thanks for your comments advocating for this feature.

Could you please share as many examples of partial / filtered / conditional uniqueness constraints or indexes here as possible?

The difficult part for this feature will definitely be to figure out if we can represent all the "checks" in a structured way in Prisma Schema Language (and hence potentially even execute them in Prisma Client, skipping the database roundtrip - but also properly maintain and migrate them) or if we "just" present them as magic SQL strings without understanding what they do, and "only" take care of migrating and introspecting them.

(Hopefully, the syntax and examples will have a lot of overlap with https://github.com/prisma/prisma/issues/3388, where I just asked a similar question)

[0xCLARITY]

My use case is this:

CREATE UNIQUE INDEX "Subscriber_email_key" ON "Subscriber"("email") WHERE "deletedAt" IS NULL;

where I would want the model to look something like this:

model Subscriber {
  id                Int     @id @default(autoincrement())
  email             String

  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  deletedAt DateTime?

  @@unique([email, deletedAt], where: { deletedAt: null })
}

16658 would also be a useful interim step, because then I could lie to Prisma by just having the unique index on email in the schema.prisma file - mark it as manually maintained - and then use extendedWhereUnique to include the deletedAt field in all my queries.

The main downsides I've run into with not having support for partial unique indexes is not being able to use upsert() or findUnique().

[LinusU]

Our use case is deletedAt fields, we are currently working around it as such:

1. Add the index in the schema without the constraint:

 model MenuCategory {
   // ...
   pageId              String
+
+  @@index([pageId], map: "kmLQh1v87l")
 }

2. Run prisma migrate dev with the --create-only flag
3. Edit the generated migration to include the WHERE clause:

 -- CreateIndex
-CREATE INDEX "kmLQh1v87l" ON "MenuCategory"("pageId");
+CREATE INDEX "kmLQh1v87l" ON "MenuCategory"("pageId") WHERE "deletedAt" IS NULL;

4. Edit the schema by adding the where clause and commenting out the index:

-  @@index([pageId], map: "kmLQh1v87l")
+  // FIXME: https://github.com/prisma/prisma/issues/6974
+  // @@index([pageId], map: "kmLQh1v87l", where: { deletedAt: null })

---

@janpio So for us it should be covered by structured data in the Prisma language. It would also be important that it works together with map (I don't see why it wouldn't) since we use that because of #15270.

[adrianescat]

Hey! my use case is this one:

status text NOT NULL CHECK (status IN ('open', 'idle', 'close')),

And I need to do more similar columns like this one. Just want to check if a column has a text equal to one of the words passed in the IN tuple

[Linloir]

Also depending on this feature for soft delete, currently working around using manually created migrations which aren't supported by current orm yet, really looking forward for official supports

[Neilshweky]

I would also like this feature

[immjs]

One example of how this might be used would be a model of phone numbers for a user, where we have a field to determine which one is the primary number:

model PhoneNumber {
  (standard id/created/updated fields)

  userId    String  @map("user_id")
  user      User    @relation(fields: [userId], references: [id])
  number    String
  isPrimary Boolean @map("is_primary")

  @@unique([userId, isPrimary], name: "uniquePrimaryForUser", where: { isPrimary: true })
  @@unique([userId, number], name: "uniqueNumberForUser")
}

In postgres, they support unique indexes with conditions. For this example, you'd create the index with:

CREATE UNIQUE INDEX phone_number_unique_primary_for_user ON "PhoneNumber"(user_id, is_primary) WHERE (is_primary = true);

This is possible within Rails' ActiveRecord validations, which I've used in the past, and something I would derive great value from also getting within Prisma.

Hello, is there a way to achieve this currently?

[Goldziher]

+1 for this

[kguirguis]

+1

[llravelo]

Hi, I would also like this feature for unique constraints on soft delete. Thanks!

[mhesham93]

It would be great if this can be modeled in prisma!

[salahmedamin]

Looking forward to see this feature in near future !

[mihoward21]

My use case is, I have a table users can submit entries into for approval (adding a label to an account). Multiple users can submit the same account_id, but only one can ever be approved at one time. So I'd like my prisma schema to look something like:

model GlobalLabelAccount {
  account_id String @db.Uuid
  account Account @relation(fields: [account_id], references: [id])
  label String
  is_approved Boolean
  @@unique([account_id, is_approved], where: { is_approved: true })
}

I'm just manually creating the migration file for now.

[seb-mynotary]

Use case : Check for unicity only when column value is not null with postgresql

Table:

CREATE TABLE example (
    id SERIAL PRIMARY KEY,
    provider_id INTEGER
);

Version 14:

CREATE UNIQUE INDEX IF NOT EXISTS index_name ON example (provider_id)
    WHERE provider_id IS NOT NULL;

Version 15:

CREATE UNIQUE INDEX index_name
ON example (provider_id) NULLS NOT DISTINCT;

[tak1n]

Check for unicity only when column value is not null with postgresql

@seb-mynotary From what I know PostgreSQL ignores null values on unique indexes:

https://www.postgresql.org/docs/current/indexes-unique.html

By default, null values in a unique column are not considered equal, allowing multiple nulls in the column. The NULLS NOT DISTINCT option modifies this and causes the index to treat nulls as equal. A multicolumn unique index will only reject cases where all indexed columns are equal in multiple rows

So either I misunderstand your example here, but what you seemingly want to achieve is already possible.

[seb-mynotary]

@tak1n

You're absolutely right, I misunderstood that part in the documentation. Thank you for pointing out to me.

Should I delete my example to keep the issue cleaner ?

[janpio]

I can hide it and just did so. Thanks for posting anyway!

[effuone]

Can't wait to see this feature soon!

[hendricksond]

Here's another use case:

Given this requirement, which is very similar to one I have in an application I maintain:

• A user can have one non-ended campaign of each type

We could represent it with this Prisma schema:

enum State {
  ACTIVE
  PAUSED
  ENDED
}

enum CampaignType {
  TYPE_1
  TYPE_2
  TYPE_3
}

model Campaign {
  id String @id @default(cuid())
  userId String
  name String
  campaignType CampaignType
  state State @default(ACTIVE) 
  @@unique([userId, campaignType], where: { state: { not: ENDED }})
}

Which would create this database index:

CREATE UNIQUE INDEX "Campaign_userId_campaignType" ON "Campaign" ("userId", "campaignType") where state <> 'ENDED';

[rpartridge]

Hi prisma team, I also have a use case for this feature, similar to comments above:

model Account {
  id @db.Uuid
  active Boolean @default(true)
  user User @relation(fields: [user_id], references: [id])
  @@unique([userId, active], where: { active: true })
  ...

}

It seems like there is a lot of demand for conditional uniqueness to be supported -- any updates on if this is on your roadmap?

[dutchsociety]

Hey Prisma Legends, I would love to have a conditional uniqueness. I understand that the value needs to be unique when filled, but would love to have it optional.

A work around am using right now is, am pushing a random generated string in. When user needs that specific info to be updated with the real value they can.

[Neamar]

@dutchsociety if you add a UNIQUE constraint on either MySQL or Postgres, they'll allow for multiple null values.

So you can simply use null rather than a random generated string.

(worth noting: since PG15 you can consider null to not be distinct for unique constraints if you need uniqueness to also respect null values, but it's not the default)

(and there is no need for conditional uniqueness here)

[dutchsociety]

@Neamar thank you for your reply, the only issue is that am a Mongo user, as far as am aware, this doesn't work?

[Neamar]

You're sadly correct.

[boredland]

You're sadly correct.

Should we probably change Prisma so, that it defaults to the partial filter expressions mentioned in that thread? My assumption would be, that Prisma users would expect Prisma to behave the same when @unique is added, no matter the underlying database semantics.

[dutchsociety]

@boredland it depends... We can also choose to stop using @unique and do extra checks in the back-end before querying.

[boredland]

@boredland it depends... We can also choose to stop using @unique and do extra checks in the back-end before querying.

Sounds slower and harder to get right, compared to an index...

[safwansamsudeen]

I'd really like this feature too - my use case is a subscription model, wherein the type ID and user ID combined should be unique only if the active field is true.

@Neamar is this is possible right now?

[Neamar]

You can create a migration manually and write the SQL directly, without changing the Prisma schema, for instance using --create-only and

CREATE UNIQUE INDEX offer_single_one_active ON offers (offer_container_id) WHERE (ended_at is null);

Prisma won't "know" about this constraint, but the DB will still enforce it. The only drawback is you can't use for instance findOne and need findFirst, since Prisma doesn't know about unicity.

[safwansamsudeen]

Ah, I see. Thanks!

[jove4015]

4. Edit the schema by adding the where clause and commenting out the index:

-  @@index([pageId], map: "kmLQh1v87l")
+  // FIXME: https://github.com/prisma/prisma/issues/6974
+  // @@index([pageId], map: "kmLQh1v87l", where: { deletedAt: null })

The problem with this approach is, Prisma no longer recognizes the unique index, so you lose the functionality that makes unique indexes helpful - no upserting, no connectOrCreate. Ultimately you fix the migration issue but lose the functionality.

Also, if you do this, it will still try to remove the manually-created index at the next migrate:

So this doesn't actually seem to work around the problem.