索引和约束

约束

SQL 约束是对表列施加的规则。它们用于防止无效数据进入数据库。

这可以确保数据库中的数据的准确性和可靠性。

默认值

DEFAULT 子句指定如果用户在进行 INSERT 操作时未提供值，则在列上使用的默认值。如果列定义不附加显式的 DEFAULT 子句，则列的默认值为 NULL。

显式的 DEFAULT 子句可以指定默认值为 NULL，字符串常量，Blob 常量，有符号数或用括号括起来的任何常量表达式。

PostgreSQL

MySQL

SQLite

import { sql } from "drizzle-orm";
import { integer, uuid, pgTable } from "drizzle-orm/pg-core";

const table = pgTable('table', {
  integer1: integer('integer1').default(42),
  integer2: integer('integer2').default(sql`'42'::integer`),
  uuid1: uuid('uuid1').defaultRandom(),
  uuid2: uuid('uuid2').default(sql`gen_random_uuid()`),
});

CREATE TABLE IF NOT EXISTS "table" (
  "integer1" integer DEFAULT 42,
  "integer2" integer DEFAULT '42'::integer,
  "uuid1" uuid DEFAULT gen_random_uuid(),
  "uuid2" uuid DEFAULT gen_random_uuid()
);

import { sql } from "drizzle-orm";
import { int, time, mysqlTable } from "drizzle-orm/mysql-core";

const table = mysqlTable("table", {
  int: int("int").default(42),
  time: time("time").default(sql`cast("14:06:10" AS TIME)`),
});

CREATE TABLE `table` (
  `int` int DEFAULT 42,
  `time` time DEFAULT cast("14:06:10" AS TIME)
);

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

const table = sqliteTable('table', {
  int1: integer('int1').default(42),
  int2: integer('int2').default(sql`(abs(42))`)
});

CREATE TABLE `table` (
  `int1` integer DEFAULT 42
  `int2` integer DEFAULT (abs(42))
);

非空

默认情况下，列可以存储 NULL 值。NOT NULL 约束强制列不接受 NULL 值。

这将确保字段始终包含一个值，这意味着您无法插入新记录或更新记录而不给此字段添加值。

PostgreSQL

MySQL

SQLite

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

const table = pgTable('table', {
  integer: integer('integer').notNull(),
});

CREATE TABLE IF NOT EXISTS "table" (
  "integer" integer NOT NULL,
);

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

const table = mysqlTable('table', {
  int: int('int').notNull(),
});

CREATE TABLE `table` (
  `int` int NOT NULL,
);

const table = sqliteTable('table', { 
  numInt: integer('numInt').notNull() 
});

CREATE TABLE table (
  `numInt` integer NOT NULL
);

唯一性

UNIQUE 约束确保列中的所有值都是不同的。

UNIQUE 约束和 PRIMARY KEY 约束都为列或一组列提供了唯一性保证。

PRIMARY KEY 约束自动具有 UNIQUE 约束。

ℹ️

每个表可以有多个 UNIQUE 约束，但每个表只能有一个 PRIMARY KEY 约束。

PostgreSQL

MySQL

SQLite

import { integer, text, unique, pgTable } from "drizzle-orm/pg-core";

export const user = pgTable('user', {
  id: integer('id').unique(),
});

export const table = pgTable('table', {
  id: integer('id').unique('custom_name'),
});

export const composite = pgTable('composite_example', {
  id: integer('id'),
  name: text('name'),
}, (t) => ({
  unq: unique().on(t.id, t.name),
  unq2: unique('custom_name').on(t.id, t.name)
}));

// In Postgres 15.0+ NULLS NOT DISTINCT is available
// This example demonstrates both available usages
export const userNulls = pgTable('user_nulls_example', {
  id: integer('id').unique("custom_name", { nulls: 'not distinct' }),
}, (t) => ({
  unq: unique().on(t.id).nullsNotDistinct()
}));

CREATE TABLE IF NOT EXISTS "composite_example" (
  "id" integer,
  "name" text,
  CONSTRAINT "composite_example_id_name_unique" UNIQUE("id","name"),
  CONSTRAINT "custom_name" UNIQUE("id","name")
);

CREATE TABLE IF NOT EXISTS "table" (
  "id" integer,
  CONSTRAINT "custom_name" UNIQUE("id")
);

CREATE TABLE IF NOT EXISTS "user" (
  "id" integer,
  CONSTRAINT "user_id_unique" UNIQUE("id")
);

CREATE TABLE IF NOT EXISTS "user_nulls_example" (
  "id" integer,
  CONSTRAINT "custom_name" UNIQUE NULLS NOT DISTINCT("id"),
  CONSTRAINT "user_nulls_example_id_unique" UNIQUE NULLS NOT DISTINCT("id")
);

import { int, varchar, unique, mysqlTable } from "drizzle-orm/mysql-core";

export const user = mysqlTable('user', {
  id: int('id').unique(),
});

export const table = mysqlTable('table', {
  id: int('id').unique('custom_name'),
});

export const composite = mysqlTable('composite_example', {
  id: int('id'),
  name: varchar('name', { length: 256 }),
}, (t) => ({
  unq: unique().on(t.id, t.name),
  unq2: unique('custom_name').on(t.id, t.name)
}));

CREATE TABLE `user` (
  `id` int,
  CONSTRAINT `user_id_unique` UNIQUE(`id`)
);

CREATE TABLE `table` (
  `id` int,
  CONSTRAINT `custom_name` UNIQUE(`id`)
);

CREATE TABLE `composite_example` (
  `id` int,
  `name` varchar(256),
  CONSTRAINT `composite_example_id_name_unique` UNIQUE(`id`,`name`),
  CONSTRAINT `custom_name` UNIQUE(`id`,`name`)
);

import { int, text, unique, sqliteTable } from "drizzle-orm/sqlite-core";

export const user = sqliteTable('user', {
  id: int('id').unique(),
});

export const table = sqliteTable('table', {
  id: int('id').unique('custom_name'),
});

export const composite = sqliteTable('composite_example', {
  id: int('id'),
  name: text('name'),
}, (t) => ({
  unq: unique().on(t.id, t.name),
  unq2: unique('custom_name').on(t.id, t.name)
}));

CREATE TABLE `user` (
    `id` integer
);

CREATE TABLE `table` (
  `id` integer
);

CREATE TABLE `composite_example` (
  `id` integer,
  `name` text
);

CREATE UNIQUE INDEX `composite_example_id_name_unique` ON `composite_example` (`id`,`name`);
CREATE UNIQUE INDEX `custom_name` ON `composite_example` (`id`,`name`);
CREATE UNIQUE INDEX `custom_name` ON `table` (`id`);
CREATE UNIQUE INDEX `user_id_unique` ON `user` (`id`);

检查条件

CHECK 约束用于限制可放置在列中的值范围。

如果在列上定义了 CHECK 约束，它将仅允许为此列指定的某些值。

如果在表上定义了 CHECK 约束，它可以根据行中其他列的值来限制特定列中的值。

🛑

DRIZZLE ORM 尚未实现此功能

主键

PRIMARY KEY 约束唯一标识表中的每条记录。主键必须包含 UNIQUE 值，并且不能包含 NULL 值。

一个表只能有一个主键；并且在该表中，此主键可以由单个字段或多个字段组成。

PostgreSQL

MySQL

SQLite

import { serial, text, pgTable } from "drizzle-orm/pg-core";

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

const table = pgTable('table', {
  id: text('cuid').primaryKey(),
});

CREATE TABLE IF NOT EXISTS "user" (
  "id" serial PRIMARY KEY,
);

CREATE TABLE IF NOT EXISTS "table" (
  "cuid" text PRIMARY KEY,
);

import { int, text, mysqlTable } from "drizzle-orm/mysql-core";

export const user = mysqlTable("user", {
  id: int("id").autoincrement().primaryKey(),
})

export const table = mysqlTable("table", {
  cuid: text("cuid").primaryKey(),
})

CREATE TABLE `user` (
  `id` int AUTO_INCREMENT PRIMARY KEY NOT NULL
);

CREATE TABLE `table` (
  `cuid` text PRIMARY KEY NOT NULL
);

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

export const user = sqliteTable("user", {
  id: integer("id").primaryKey(),
})

export const pet = sqliteTable("pet", {
  id: integer("id").primaryKey(),
})

CREATE TABLE `user` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL
);

CREATE TABLE `pet` (
    `id` integer PRIMARY KEY AUTOINCREMENT
)

复合主键

与 PRIMARY KEY 类似，复合主键使用多个字段在表中唯一标识每条记录。

Drizzle ORM 提供了一个独立的 primaryKey 运算符来实现这一点：

PostgreSQL

MySQL

SQLite

import { serial, text, integer, primaryKey, pgTable } from "drizzle-orm/pg-core";

export const user = pgTable("user", {
  id: serial("id").primaryKey(),
  name: text("name"),
});

export const book = pgTable("book", {
  id: serial("id").primaryKey(),
  name: text("name"),
});

export const booksToAuthors = pgTable("books_to_authors", {
  authorId: integer("author_id"),
  bookId: integer("book_id"),
}, (table) => {
  return {
    pk: primaryKey({ columns: [table.bookId, table.authorId] }),
    pkWithCustomName: primaryKey({ name: 'custom_name', columns: [table.bookId, table.authorId] }),
  };
});

...

CREATE TABLE IF NOT EXISTS "books_to_authors" (
  "author_id" integer,
  "book_id" integer,
  PRIMARY KEY("book_id","author_id"),
);

ALTER TABLE "books_to_authors" ADD CONSTRAINT "custom_name" PRIMARY KEY("book_id","author_id");

import { int, text, primaryKey, mysqlTable } from "drizzle-orm/mysql-core";

export const user = mysqlTable("user", {
  id: int("id").autoincrement().primaryKey(),
  name: text("name"),
});

export const book = mysqlTable("book", {
  id: int("id").autoincrement().primaryKey(),
  name: text("name"),
});

export const booksToAuthors = mysqlTable("books_to_authors", {
  authorId: int("author_id"),
  bookId: int("book_id"),
}, (table) => {
  return {
    pk: primaryKey({ columns: [table.bookId, table.authorId] }),
    pkWithCustomName: primaryKey({ name: 'custom_name', columns: [table.bookId, table.authorId] }),
  };
});

...

CREATE TABLE `books_to_authors` (
  `author_id` int,
  `book_id` int,
  PRIMARY KEY(`book_id`,`author_id`)
);

import { integer, text, primaryKey, sqliteTable} from "drizzle-orm/sqlite-core";

export const user = sqliteTable("user", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  name: text("name"),
});

export const book = sqliteTable("book", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  name: text("name"),
});

export const bookToAuthor = sqliteTable("book_to_author", {
  authorId: integer("author_id"),
  bookId: integer("book_id"),
}, (table) => {
  return {
    pk: primaryKey({ columns: [table.bookId, table.authorId] }),
    pkWithCustomName: primaryKey({ name: 'custom_name', columns: [table.bookId, table.authorId] }),
  };
});

...

CREATE TABLE `book_to_author` (
  `author_id` integer,
  `book_id` integer,
  PRIMARY KEY(`book_id`, `author_id`)
);

外键

FOREIGN KEY 约束用于防止破坏表之间链接的操作。 FOREIGN KEY 是一个字段（或一组字段），它引用另一个表中的 PRIMARY KEY。具有外键的表称为子表，具有主键的表称为引用表或父表。

Drizzle ORM 提供了几种声明外键的方法。您可以在列声明语句中声明它们：

PostgreSQL

MySQL

SQLite

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

export const user = pgTable("user", {
  id: serial("id"),
  name: text("name"),
});

export const book = pgTable("book", {
  id: serial("id"),
  name: text("name"),
  authorId: integer("author_id").references(() => user.id)
});

import { int, text, mysqlTable } from "drizzle-orm/mysql-core";

export const user = mysqlTable("user", {
  id: int("id").primaryKey().autoincrement(),
  name: text("name"),
});

export const book = mysqlTable("book", {
  id: int("id").primaryKey().autoincrement(),
  name: text("name"),
  authorId: int("author_id").references(() => user.id)
});

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

export const user = sqliteTable("user", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  name: text("name"),
});

export const book = sqliteTable("book", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  name: text("name"),
  authorId: integer("author_id").references(() => user.id)
});

如果您想进行自引用，由于 TypeScript 的限制，您必须显式设置引用回调的返回类型或使用一个独立的 foreignKey 运算符。

PostgreSQL

MySQL

SQLite

import { serial, text, integer, foreignKey, pgTable, AnyPgColumn } from "drizzle-orm/pg-core";

export const user = pgTable("user", {
  id: serial("id"),
  name: text("name"),
  parentId: integer("parent_id").references((): AnyPgColumn => user.id)
});

// or
export const user = pgTable("user", {
  id: serial("id"),
  name: text("name"),
  parentId: integer("parent_id"),
}, (table) => {
  return {
    parentReference: foreignKey({
      columns: [table.parentId],
      foreignColumns: [table.id],
      name: "custom_fk"
    }),
  };
});

import { int, text, foreignKey, AnyMySqlColumn, mysqlTable } from "drizzle-orm/mysql-core";

export const user = mysqlTable("user", {
  id: int("id").primaryKey().autoincrement(),
  name: text("name"),
  parentId: int("parent_id").references((): AnyMySqlColumn => user.id),
});

// or
export const user = mysqlTable("user", {
  id: int("id").primaryKey().autoincrement(),
  name: text("name"),
  parentId: int("parent_id")
}, (table) => {
  return {
    parentReference: foreignKey({
      columns: [table.parentId],
      foreignColumns: [table.id],
      name: "custom_fk"
    }),
  };
});

import { integer, text, foreignKey, sqliteTable, AnySQLiteColumn } from "drizzle-orm/sqlite-core";

export const user = sqliteTable("user", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  name: text("name"),
  parentId: integer("parent_id").references((): AnySQLiteColumn => user.id)
});

//or
export const user = sqliteTable("user", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  name: text("name"),
  parentId: integer("parent_id"),
}, (table) => {
  return {
    parentReference: foreignKey({
      columns: [table.parentId],
      foreignColumns: [table.id],
      name: "custom_fk"
    }),
  };
});

要声明多列外键，可以使用专用的 foreignKey 运算符：

PostgreSQL

MySQL

SQLite

import { serial, text, foreignKey, pgTable, AnyPgColumn } from "drizzle-orm/pg-core";

export const user = pgTable("user", {
  firstName: text("firstName"),
  lastName: text("lastName"),
}, (table) => {
  return {
    pk: primaryKey({ columns: [table.firstName, table.lastName]}),
  };
});

export const profile = pgTable("profile", {
  id: serial("id").primaryKey(),
  userFirstName: text("user_first_name"),
  userLastName: text("user_last_name"),
}, (table) => {
  return {
    userReference: foreignKey({
      columns: [table.userFirstName, table.userLastName],
      foreignColumns: [user.firstName, user.lastName]
      name: "custom_fk"
    })
  }
})

import { int, text, primaryKey, foreignKey, mysqlTable, AnyMySqlColumn } from "drizzle-orm/mysql-core";

export const user = mysqlTable("user", {
  firstName: text("firstName"),
  lastName: text("lastName"),
}, (table) => {
  return {
    pk: primaryKey({ columns: [table.firstName, table.lastName]}),
  };
});

export const profile = mysqlTable("profile", {
  id: int("id").autoincrement().primaryKey(),
  userFirstName: text("user_first_name"),
  userLastName: text("user_last_name"),
}, (table) => {
  return {
    userReference: foreignKey({
      columns: [table.userFirstName, table.userLastName],
      foreignColumns: [user.firstName, user.lastName],
      name: "custom_name"
    })
  }
});

import { integer, text, primaryKey, foreignKey, sqliteTable, AnySQLiteColumn } from "drizzle-orm/sqlite-core";

export const user = sqliteTable("user", {
  firstName: text("firstName"),
  lastName: text("lastName"),
}, (table) => {
  return {
    pk: primaryKey({ columns: [table.firstName, table.lastName]}),
  };
});

export const profile = sqliteTable("profile", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  userFirstName: text("user_first_name"),
  userLastName: text("user_last_name"),
}, (table) => {
  return {
    userReference: foreignKey({
      columns: [table.userFirstName, table.userLastName],
      foreignColumns: [user.firstName, user.lastName],
      name: "custom_name"
    })
  }
});

索引

Drizzle ORM 提供了对索引和唯一索引的 API 声明：

PostgreSQL

MySQL

SQLite

import { serial, text, index, uniqueIndex, pgTable } from "drizzle-orm/pg-core";

export const user = pgTable("user", {
  id: serial("id").primaryKey(),
  name: text("name"),
  email: text("email"),
}, (table) => {
  return {
    nameIdx: index("name_idx").on(table.name),
    emailIdx: uniqueIndex("email_idx").on(table.email),
  };
});

CREATE TABLE "user" (
  ...
);

CREATE INDEX "name_idx" ON "user" ("name");
CREATE UNIQUE INDEX "email_idx" ON "user" ("email");

⚠️

对于 drizzle-kit@0.22.0 和 drizzle-orm@0.31.0 drizzle-kit 之前的版本仅支持索引 name 和 on() 参数。

在 drizzle-kit@0.22.0 和 drizzle-orm@0.31.0 版本之后，drizzle-kit 中支持所有字段！

从 0.31.0 开始，Drizzle ORM 的新索引 API 提供了用于创建索引的所有参数集：

// First example, with `.on()`
index('name')
  .on(table.column1.asc(), table.column2.nullsFirst(), ...) or .onOnly(table.column1.desc().nullsLast(), table.column2, ...)
  .concurrently()
  .where(sql``)
  .with({ fillfactor: '70' })

// Second Example, with `.using()`
index('name')
  .using('btree', table.column1.asc(), sql`lower(${table.column2})`, table.column1.op('text_ops'))
  .where(sql``) // sql expression
  .with({ fillfactor: '70' })

import { int, text, index, uniqueIndex, mysqlTable } from "drizzle-orm/mysql-core";

export const user = mysqlTable("user", {
  id: int("id").primaryKey().autoincrement(),
  name: text("name"),
  email: text("email"),
}, (table) => {
  return {
    nameIdx: index("name_idx").on(table.name),
    emailIdx: uniqueIndex("email_idx").on(table.email),
  };
});

CREATE TABLE `user` (
  ...
);

CREATE INDEX `name_idx` ON `user` (`name`);
CREATE UNIQUE INDEX `email_idx` ON `user` (`email`);

⚠️

目前的 drizzle-kit 只支持索引的 name 和 on() 参数。

Drizzle ORM 提供了用于索引创建的所有参数：

// Index declaration reference
index("name")
  .on(table.name)
  .algorythm("default") // "default" | "copy" | "inplace"
  .using("btree") // "btree" | "hash"
  .lock("default") // "none" | "default" | "exclusive" | "shared"

import { integer, text, index, uniqueIndex, sqliteTable } from "drizzle-orm/sqlite-core";

export const user = sqliteTable("user", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  name: text("name"),
  email: text("email"),
}, (table) => {
  return {
    nameIdx: index("name_idx").on(table.name),
    emailIdx: uniqueIndex("email_idx").on(table.email),
  };
});

CREATE TABLE `user` (
  ...
);

CREATE INDEX `name_idx` ON `user` (`name`);
CREATE UNIQUE INDEX `email_idx` ON `user` (`email`);

Drizzle ORM 提供了用于索引创建的所有参数：

// 索引声明参考
index("name")
  .on(table.name)
  .where(sql`...`)

Become a Gold Sponsor