Many-to-many join tables
A junction table must prevent duplicate associations between two entities.
Drizzle ORM · Function
primaryKey
primaryKey defines a table-level primary-key constraint, especially for composite keys. Pass the participating columns and optionally a custom constraint name.
How it works
The standalone primaryKey operator declares a table-level composite primary key by receiving the participating columns. It can also receive a custom constraint name, while a column-level .primaryKey() is used for a single-column key.
Syntax
primaryKey({ columns: [table.columnA, table.columnB], name?: "constraint_name" })Parameters
| Name | Type | Description |
|---|---|---|
columns | array of table columns | Columns that jointly make up the primary key. |
name | string | Optional database constraint name. |
Returns
A table-level primary-key constraint definition used in the table callback.
Key points
- The standalone operator is used when the primary key combines multiple table columns.
- Pass the participating columns through the
columnsproperty as an array. - Supply
namewhen the database constraint should use an explicit identifier. - A composite key identifies a row through the combined values of several fields.
Common mistakes
- A table may define only one primary key, although that key can span several columns.
- Every primary-key value must be unique and non-null.
Example: Define a composite primary key
import { integer, pgTable, primaryKey, text } from "drizzle-orm/pg-core";
export const booksToAuthors = pgTable("books_to_authors", {
bookId: integer("book_id").notNull(),
authorId: integer("author_id").notNull(),
note: text("note"),
}, (table) => [
primaryKey({ columns: [table.bookId, table.authorId] }),
]);This junction table uses both foreign-key identifiers together as its composite primary key, preventing duplicate book-author pairs.
When to use it
Composite identity
A record is uniquely identified by the combination of two or more fields.
Explicit constraint naming
Generated constraint names may be too long or inconsistent across environments.
Interview questions
QHow do you define a composite primary key in Drizzle?
Use primaryKey({ columns: [table.bookId, table.authorId] }) in the table callback to define a composite key. The columns together uniquely identify each row.
Follow-up: How would you control the generated constraint name? Pass name: 'custom_name' alongside the columns array.
QWhat database guarantees does a primary key provide?
A primary key identifies records and enforces uniqueness; its values cannot be null. Drizzle’s standalone operator is intended for keys spanning multiple columns.
Follow-up: Can a table have two primary keys? No. It can have one primary-key constraint, whether single-column or composite.
QWhere does the standalone primaryKey operator belong?
Place primaryKey in the table callback and import it from the database-specific core package, such as drizzle-orm/pg-core. The operator accepts a columns array and optionally a custom name.
Follow-up: Why name the constraint explicitly? A custom name can avoid problems when automatically generated names exceed a database’s identifier-length limit.
Related
foreignKeyuniqueindexesnotNull