Prisma @unique to SQL UNIQUE Constraint

Single-Field Unique Constraints

Prisma's @unique attribute on a field translates directly to a SQL UNIQUE constraint on the corresponding column.

Example Prisma Schema

model Account {
  id       Int    @id @default(autoincrement())
  email    String @unique
  username String @unique
  slug     String @unique
  name     String

  @@map("accounts")
}

Generated SQL

CREATE TABLE accounts (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  username VARCHAR(255) NOT NULL UNIQUE,
  slug VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL
);

How UNIQUE Works in SQL

A UNIQUE constraint ensures that no two rows in the table have the same value in that column. The database automatically creates a unique index to enforce this constraint. Key behaviors:

• NULL handling: In most databases, multiple NULL values are allowed in a UNIQUE column (since NULL is not equal to NULL). However, this varies by dialect.
• Error on duplicate: Attempting to INSERT or UPDATE a row with a duplicate value in a UNIQUE column raises a constraint violation error.
• Performance: The unique index also speeds up lookups on that column.

UNIQUE on Optional Fields

When a Prisma field is optional (?) and unique:

model Profile {
  id       Int     @id @default(autoincrement())
  nickname String? @unique
}

This generates a column that is both nullable and unique. Most databases allow multiple NULL values in a UNIQUE column — only non-NULL values must be unique.

Difference from Composite UNIQUE

The @unique attribute applies to a single column. For multi-column uniqueness (e.g., unique combination of firstName and lastName), use @@unique at the model level. See the composite unique example for details.