I’m comparing the output of a schema created with prisma using introspection to the original schema in PostgreSQL, and something with unique constraints stuck out to me. When you create a table using

UNIQUE

in postgres, it creates a unique constraint. With Prisma and

@@unique

, it just creates a unique index. For example

model AnotherUser {
  id        Int     @id @default(autoincrement())
  firstName String?
  lastName  String?

  @@unique([firstName, lastName])
}

Generates the following migration

-- CreateTable
CREATE TABLE "AnotherUser" (
    "id" SERIAL NOT NULL,
    "firstName" TEXT,
    "lastName" TEXT,

    CONSTRAINT "AnotherUser_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "AnotherUser_firstName_lastName_key" ON "AnotherUser"("firstName", "lastName");

And the table description lists it as simply UNIQUE

"AnotherUser_firstName_lastName_key" UNIQUE, btree ("firstName", "lastName")

And I don’t see a constraint in

pg_constraint

. BUT if I create the same table with

UNIQUE

CREATE TABLE "AnotherUser" (
    "id" SERIAL NOT NULL,
    "firstName" TEXT,
    "lastName" TEXT,

    CONSTRAINT "AnotherUser_pkey" PRIMARY KEY ("id"),
    UNIQUE("firstName", "lastName")
);

The table description lists the unique constraint

"AnotherUser_firstName_lastName_key" UNIQUE CONSTRAINT, btree ("firstName", "lastName")

And I can see the constraint in

pg_constraint

Does anyone know why Prisma Migrate is only creating unique indexes and not unique constraints (which are backed by a unique index)?

Hi Jeff 👋 Are you seeing this difference in the latest Prisma version? I am not exactly sure about the reasoning behind the difference between schemas but I can investigate and let you know.

Yeah, this is with Prisma 4.3.1

$ npx prisma --version                                                                                                                                                                                                                                                                        
Environment variables loaded from .env
prisma                  : 4.3.1
@prisma/client          : 4.0.0
Current platform        : darwin
Query Engine (Node-API) : libquery-engine c875e43600dfe042452e0b868f7a48b817b9640b (at node_modules/@prisma/engines/libquery_engine-darwin.dylib.node)
Migration Engine        : migration-engine-cli c875e43600dfe042452e0b868f7a48b817b9640b (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine    : introspection-core c875e43600dfe042452e0b868f7a48b817b9640b (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary           : prisma-fmt c875e43600dfe042452e0b868f7a48b817b9640b (at node_modules/@prisma/engines/prisma-fmt-darwin)
Format Wasm             : @prisma/prisma-fmt-wasm 4.3.0-32.c875e43600dfe042452e0b868f7a48b817b9640b
Default Engines Hash    : c875e43600dfe042452e0b868f7a48b817b9640b
Studio                  : 0.473.0

Hi Jeff, After confirming this with the engineering team, we can say that this behaviour is expected. We haven’t considered it a problem so far because while unique indexes and unique constraints are different in the catalogue on Postgres, we haven’t seen any way they behave differently in the context of a DML query.