Constraints

Define primary keys, foreign keys, unique constraints, and check constraints in Drizzle ORM.

Constraints ensure data integrity by enforcing rules on columns and tables. Drizzle supports primary keys, foreign keys, unique constraints, and check constraints.

Primary keys

Column-level primary key

The simplest way to define a primary key is on a single column:

PostgreSQL

import { pgTable, serial, uuid } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
});

// Or with UUID
export const sessions = pgTable('sessions', {
  id: uuid('id').defaultRandom().primaryKey(),
});

MySQL

import { mysqlTable, serial, int } from 'drizzle-orm/mysql-core';

export const users = mysqlTable('users', {
  id: serial('id').primaryKey(),
});

// Or with regular int
export const products = mysqlTable('products', {
  id: int('id').primaryKey(),
});

SQLite

import { sqliteTable, integer } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
});

Composite primary key

Define a primary key across multiple columns:

PostgreSQL

import { pgTable, integer, varchar, primaryKey } from 'drizzle-orm/pg-core';

export const userRoles = pgTable('user_roles', {
  userId: integer('user_id').notNull(),
  roleId: integer('role_id').notNull(),
}, (table) => [
  primaryKey({ columns: [table.userId, table.roleId] }),
]);

// With custom name
export const userRoles = pgTable('user_roles', {
  userId: integer('user_id').notNull(),
  roleId: integer('role_id').notNull(),
}, (table) => [
  primaryKey({ name: 'user_role_pk', columns: [table.userId, table.roleId] }),
]);

Foreign keys

Column-level foreign key

Define a foreign key reference directly on a column:

import { pgTable, serial, integer, varchar } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 255 }),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }),
  authorId: integer('author_id').references(() => users.id),
});

Foreign key with actions

Specify ON DELETE and ON UPDATE actions:

import { pgTable, serial, integer } from 'drizzle-orm/pg-core';

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  authorId: integer('author_id')
    .references(() => users.id, { onDelete: 'cascade', onUpdate: 'cascade' }),
});

Available actions:

• 'cascade' - Delete/update related rows
• 'restrict' - Prevent delete/update if related rows exist
• 'no action' - Same as restrict (default)
• 'set null' - Set foreign key to NULL
• 'set default' - Set foreign key to default value

Table-level foreign key

Define foreign keys in the table’s extra config:

import { pgTable, serial, integer, varchar, foreignKey } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }),
  authorId: integer('author_id').notNull(),
}, (table) => [
  foreignKey({
    columns: [table.authorId],
    foreignColumns: [users.id],
  }).onDelete('cascade'),
]);

Composite foreign key

Reference multiple columns:

import { pgTable, integer, varchar, foreignKey } from 'drizzle-orm/pg-core';

export const countries = pgTable('countries', {
  id: integer('id').primaryKey(),
  code: varchar('code', { length: 2 }).notNull(),
});

export const cities = pgTable('cities', {
  id: integer('id').primaryKey(),
  countryId: integer('country_id').notNull(),
  countryCode: varchar('country_code', { length: 2 }).notNull(),
}, (table) => [
  foreignKey({
    columns: [table.countryId, table.countryCode],
    foreignColumns: [countries.id, countries.code],
  }),
]);

Named foreign key

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  authorId: integer('author_id').notNull(),
}, (table) => [
  foreignKey({
    name: 'posts_author_fk',
    columns: [table.authorId],
    foreignColumns: [users.id],
  }),
]);

Unique constraints

Column-level unique

Mark a single column as unique:

import { pgTable, serial, varchar } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  username: varchar('username', { length: 50 }).unique(),
});

Table-level unique constraint

Define unique constraints on single or multiple columns:

import { pgTable, serial, varchar, unique } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull(),
  username: varchar('username', { length: 50 }).notNull(),
}, (table) => [
  unique().on(table.email),
  unique('username_unique').on(table.username),
]);

Composite unique constraint

Enforce uniqueness across multiple columns:

import { pgTable, serial, varchar, integer, unique } from 'drizzle-orm/pg-core';

export const userProfiles = pgTable('user_profiles', {
  id: serial('id').primaryKey(),
  userId: integer('user_id').notNull(),
  platform: varchar('platform', { length: 50 }).notNull(),
  profileUrl: varchar('profile_url', { length: 255 }),
}, (table) => [
  unique('user_platform_unique').on(table.userId, table.platform),
]);

NULLS NOT DISTINCT (PostgreSQL)

By default, PostgreSQL considers NULL values as distinct in unique constraints. Use nullsNotDistinct() to treat NULL values as equal:

import { pgTable, serial, varchar, unique } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }),
  alternateEmail: varchar('alternate_email', { length: 255 }),
}, (table) => [
  unique().on(table.email).nullsNotDistinct(),
]);

nullsNotDistinct() is only available in PostgreSQL 15+

Check constraints

Check constraints validate data based on a boolean expression:

Basic check constraint

import { pgTable, serial, integer, check, sql } from 'drizzle-orm/pg-core';

export const products = pgTable('products', {
  id: serial('id').primaryKey(),
  price: integer('price').notNull(),
  discount: integer('discount'),
}, (table) => [
  check('price_positive', sql`${table.price} > 0`),
  check('discount_valid', sql`${table.discount} >= 0 AND ${table.discount} <= 100`),
]);

Multiple column check

import { pgTable, serial, timestamp, check, sql } from 'drizzle-orm/pg-core';

export const events = pgTable('events', {
  id: serial('id').primaryKey(),
  startDate: timestamp('start_date').notNull(),
  endDate: timestamp('end_date').notNull(),
}, (table) => [
  check('valid_date_range', sql`${table.endDate} > ${table.startDate}`),
]);

Complex check constraint

import { pgTable, serial, varchar, integer, check, sql } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }),
  phone: varchar('phone', { length: 20 }),
  age: integer('age'),
}, (table) => [
  check('contact_required', sql`${table.email} IS NOT NULL OR ${table.phone} IS NOT NULL`),
  check('adult_only', sql`${table.age} >= 18`),
]);

Check constraints are evaluated for each row. Complex checks can impact performance on large tables.

Complete example

import { 
  pgTable, 
  serial, 
  varchar, 
  integer, 
  timestamp,
  boolean,
  foreignKey,
  unique,
  check,
  sql 
} from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  username: varchar('username', { length: 50 }).notNull(),
  age: integer('age'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => [
  unique('username_unique').on(table.username),
  check('adult_user', sql`${table.age} >= 18`),
]);

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  authorId: integer('author_id').notNull(),
  editorId: integer('editor_id'),
  published: boolean('published').default(false),
  views: integer('views').default(0),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => [
  foreignKey({
    columns: [table.authorId],
    foreignColumns: [users.id],
  }).onDelete('cascade'),
  foreignKey({
    columns: [table.editorId],
    foreignColumns: [users.id],
  }).onDelete('set null'),
  check('positive_views', sql`${table.views} >= 0`),
]);

export const postTags = pgTable('post_tags', {
  postId: integer('post_id').notNull().references(() => posts.id),
  tagId: integer('tag_id').notNull(),
}, (table) => [
  primaryKey({ columns: [table.postId, table.tagId] }),
]);