Single-column uniqueness
Prevent duplicate email addresses in a users table.
Drizzle ORM · Function
unique
Drizzle’s unique declaration adds a database constraint that rejects duplicate values in one column or a combination of columns. It can be named and is commonly used in a table callback for composite uniqueness.
How it works
Drizzle’s unique declaration creates a database-level uniqueness constraint for one or more columns. Use it in a table callback for composite constraints, optionally give the constraint a name, and use the PostgreSQL-specific modifier when the documentation’s NULL handling option is required.
Syntax
unique(name?).on(...columns)Parameters
| Name | Type | Description |
|---|---|---|
name | string | undefined | Optional constraint name; omit it for an automatically named constraint. |
columns | AnyColumn[] | One or more table columns covered by the constraint. |
Returns
A table constraint declaration used in the table schema definition.
Key points
- A unique constraint prevents duplicate values for the constrained column.
- Uniqueness can apply to either one column or a composite set of columns.
- A table may define multiple unique constraints even though it can have only one primary key.
- PostgreSQL supports column-level declarations for single columns and table-level declarations for composite constraints.
Common mistakes
- SQLite treats unique constraints as unique indexes internally, so constraint behavior and naming follow SQLite’s unique-index model.
- Do not rely on the PostgreSQL
NULLS NOT DISTINCToption when defining a MySQL unique constraint.
Example: Named single-column unique constraint
import { pgTable, text, unique } from "drizzle-orm/pg-core";
export const users = pgTable("users", {
id: text("id").notNull(),
email: text("email").notNull(),
}, (table) => ({
emailNameUnique: unique("users_email_unique").on(table.email),
}));Defines a PostgreSQL table whose email column must be unique, using a named table-level constraint.
When to use it
Composite uniqueness
Require each pair of values, such as tenant and slug, to be unique.
Named constraints
Give a migration-visible constraint a predictable identifier.
Interview questions
QWhat does Drizzle’s unique declaration do?
The unique declaration adds a database uniqueness constraint over one or more columns. A named constraint can be created with unique("name").on(table.column).
Follow-up: How would you enforce uniqueness across two columns? Use unique("name").on(table.first, table.second) in the table callback.
QHow do column-level and table-level unique constraints differ?
A column-level .unique() is suitable for a single column. The table callback form, unique().on(...), supports multi-column uniqueness and can also assign a custom constraint name.
Follow-up: Why use a named constraint? A stable name makes generated schema and migration output easier to identify and manage.
QWhat is the PostgreSQL-specific null-handling option?
PostgreSQL supports the NULLS NOT DISTINCT behavior through the unique-constraint API. This restricts a table from having more than one null value for the constrained columns.
Follow-up: Is this portable to MySQL? No; the documentation states that MySQL does not support this option.
Related
unique indexprimary keypgTablemysqlTablesqliteTable