singlestore/constraints

10 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

DEFAULT constraint in SingleStore

The DEFAULT clause specifies a default value for a column if no value is provided during an INSERT. If no explicit DEFAULT clause is attached, the default value is NULL. An explicit DEFAULT can specify NULL, a string constant, a blob constant, a signed number, or any constant expression in parentheses.

DEFAULT constraint example with int and time

Example of using DEFAULT in Drizzle ORM for SingleStore: 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)`), }); This generates: CREATE TABLE `table` (`int` int DEFAULT 42, `time` time DEFAULT cast("14:06:10" AS TIME));

NOT NULL constraint in SingleStore

The NOT NULL constraint enforces that a column cannot accept NULL values. By default, a column can hold NULL values. With NOT NULL applied, you cannot insert a new record or update a record without adding a value to that field.

NOT NULL constraint example

Example of using NOT NULL in Drizzle ORM for SingleStore: import { int, singlestoreTable } from "drizzle-orm/singlestore-core"; const table = singlestoreTable('table', { int: int('int').notNull(), }); This generates: CREATE TABLE `table` (`int` int NOT NULL);

UNIQUE constraint in SingleStore

The UNIQUE constraint ensures that all values in a column are different. Both UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness on a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint. A table can have many UNIQUE constraints but only one PRIMARY KEY constraint.

UNIQUE constraint examples single and composite

Examples of UNIQUE constraints in Drizzle ORM for SingleStore: Single column unique: 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'), }); Composite unique: 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) ]); Generated SQL: 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 constraint in SingleStore

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, which can consist of a single column or multiple columns.

PRIMARY KEY constraint examples

Examples of PRIMARY KEY in Drizzle ORM for SingleStore: 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(), }); Generated SQL: CREATE TABLE `user` (`id` int AUTO_INCREMENT PRIMARY KEY NOT NULL); CREATE TABLE `table` (`cuid` text PRIMARY KEY NOT NULL);

COMPOSITE PRIMARY KEY in SingleStore

A composite primary key uniquely identifies each record in a table using multiple fields. Drizzle ORM provides a standalone primaryKey operator for this purpose.

COMPOSITE PRIMARY KEY example

Example of composite PRIMARY KEY in Drizzle ORM for SingleStore: 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] }), primaryKey({ name: 'custom_name', columns: [table.bookId, table.authorId] }), ]); Generated SQL: CREATE TABLE `books_to_authors` (`author_id` int, `book_id` int, PRIMARY KEY(`book_id`,`author_id`));

Give your agent this brain