Indexes & Constraints

Constraints

SQL constraints are the rules enforced on table columns. They are used to prevent invalid data from being entered into the database.

This ensures the accuracy and reliability of your data in the database.

Default

The DEFAULT clause specifies a default value to use for the column if no value provided by the user when doing an INSERT. If there is no explicit DEFAULT clause attached to a column definition, then the default value of the column is NULL.

An explicit DEFAULT clause may specify that the default value is NULL, a string constant, a blob constant, a signed-number, or any constant expression enclosed in parentheses.

import { sql } from "drizzle-orm";
import { int, time, singlestoreTable } from "drizzle-orm/singlestore-core";

const table = singlestoreTable("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)
);

Not null

By default, a column can hold NULL values. The NOT NULL constraint enforces a column to NOT accept NULL values.

This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.

import { int, singlestoreTable } from "drizzle-orm/singlestore-core";

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

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

Unique

The UNIQUE constraint ensures that all values in a column are different.

Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint.

You can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

import { int, varchar, unique, singlestoreTable } from "drizzle-orm/singlestore-core";

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

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

export const composite = singlestoreTable('composite_example', {
  id: int('id'),
  name: varchar('name', { length: 256 }),
}, (t) => [
  unique().on(t.id, t.name),
  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`)
);

Primary Key

The PRIMARY KEY constraint uniquely identifies each record in a table.
Primary keys must contain UNIQUE values, and cannot contain NULL values.

A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

import { int, text, singlestoreTable } from "drizzle-orm/singlestore-core";

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

export const table = singlestoreTable("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
);

Composite Primary Key

Just like PRIMARY KEY, composite primary key uniquely identifies each record in a table using multiple fields.

Drizzle ORM provides a standalone primaryKey operator for that:

import { int, text, primaryKey, singlestoreTable } from "drizzle-orm/singlestore-core";

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

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

export const booksToAuthors = singlestoreTable("books_to_authors", {
  authorId: int("author_id"),
  bookId: int("book_id"),
}, (table) => [
  primaryKey({ columns: [table.bookId, table.authorId] }),
  // Or PK with custom name
  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`)
);

Indexes

Drizzle ORM provides API for both index and unique index declaration:

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

export const user = singlestoreTable("user", {
  id: int("id").primaryKey().autoincrement(),
  name: text("name"),
  email: text("email"),
}, (table) => [
  index("name_idx").on(table.name),
  uniqueIndex("email_idx").on(table.email),
]);

CREATE TABLE `user` (
  ...
);

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