mssql/constraints

18 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 MSSQL

The DEFAULT clause specifies a default value to use for a column if no value is provided during an INSERT. If there is no explicit DEFAULT clause, the default value is NULL. An explicit DEFAULT clause may specify NULL, a string constant, a blob constant, a signed number, or any constant expression enclosed in parentheses. In Drizzle, use the .default() method on a column definition to set a default value.

NOT NULL constraint in MSSQL

The NOT NULL constraint enforces a column to NOT accept NULL values. It ensures a field always contains a value, meaning you cannot insert or update a record without providing a value for that field. In Drizzle, use the .notNull() method on a column definition.

UNIQUE constraint in MSSQL

The UNIQUE constraint ensures that all values in a column are different. You can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table. In Drizzle, use the .unique() method on a single column, or use the unique() operator with .on() method for composite unique constraints. You can optionally provide a custom constraint name.

CHECK constraint in MSSQL

The CHECK constraint is used to limit the value range that can be placed in a column. If defined on a column, it allows only certain values. If defined on a table, it can limit values in certain columns based on values in other columns in the row. In Drizzle, use the check() operator with a constraint name and a SQL condition.

PRIMARY KEY constraint in MSSQL

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 or multiple columns. In Drizzle, use the .primaryKey() method on a column definition.

Composite PRIMARY KEY in MSSQL

A composite primary key uniquely identifies each record in a table using multiple fields. In Drizzle, use the primaryKey() operator with a columns array in the table's constraint definition. You can optionally provide a custom constraint name with the name property.

FOREIGN KEY constraint in MSSQL

The FOREIGN KEY constraint prevents actions that would destroy links between tables. A FOREIGN KEY is a field or collection of fields in one table that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.

Foreign key declaration in column in MSSQL

In Drizzle MSSQL, you can declare a foreign key directly in a column definition using the .references() method, which takes a callback returning the referenced column. For example: authorId: int('author_id').references(() => user.id)

Self-referencing foreign key in MSSQL

For self-referencing foreign keys in MSSQL, due to TypeScript limitations, you must either explicitly set a return type for the reference callback using AnyMsSqlColumn type, or use a standalone foreignKey() operator in the table's constraint definition.

Multi-column foreign key in MSSQL

To declare multi-column foreign keys in Drizzle MSSQL, use the foreignKey() operator with columns and foreignColumns arrays. You can optionally provide a custom constraint name with the name property.

DEFAULT constraint example in MSSQL

Example of using DEFAULT constraint in Drizzle MSSQL: int().default(42) creates a column with default value 42. time().default(sql`cast('14:06:10' AS TIME)`) creates a column with a SQL expression as default value.

UNIQUE constraint example in MSSQL

Example of UNIQUE constraint in Drizzle MSSQL: ```typescript import { int, varchar, unique, mssqlTable } from "drizzle-orm/mssql-core"; export const user = mssqlTable('user', { id: int().unique(), }); export const table = mssqlTable('table', { id: int().unique('custom_name_1'), }); export const composite = mssqlTable('composite_example', { id: int(), name: varchar({ length: 256 }), }, (t) => [ unique().on(t.id, t.name), unique('custom_name_2').on(t.id, t.name) ]); ```

CHECK constraint example in MSSQL

Example of CHECK constraint in Drizzle MSSQL: ```typescript import { sql } from "drizzle-orm"; import { check, int, mssqlTable, text } from "drizzle-orm/mssql-core"; export const users = mssqlTable( "users", { id: int().primaryKey(), username: text().notNull(), age: int(), }, (table) => [ check("age_check1", sql`${table.age} > 21`) ] ); ```

Foreign key example in MSSQL with column reference

Example of foreign key in column definition in Drizzle MSSQL: ```typescript import { int, text, mssqlTable } from "drizzle-orm/mssql-core"; export const user = mssqlTable("user", { id: int().primaryKey().identity(), name: text(), }); export const book = mssqlTable("book", { id: int().primaryKey().identity(), name: text(), authorId: int("author_id").references(() => user.id) }); ```

Self-referencing foreign key example in MSSQL

Example of self-referencing foreign key in Drizzle MSSQL: ```typescript import { int, text, foreignKey, type AnyMsSqlColumn, mssqlTable } from "drizzle-orm/mssql-core"; export const user = mssqlTable("user", { id: int().primaryKey().identity(), name: text(), parentId: int("parent_id").references((): AnyMsSqlColumn => user.id), }); // or using standalone foreignKey operator export const user = mssqlTable("user", { id: int().primaryKey().identity(), name: text(), parentId: int("parent_id") }, (table) => [ foreignKey({ columns: [table.parentId], foreignColumns: [table.id], name: "custom_fk" }) ]); ```

Multi-column foreign key example in MSSQL

Example of multi-column foreign key in Drizzle MSSQL: ```typescript import { int, varchar, primaryKey, foreignKey, mssqlTable } from "drizzle-orm/mssql-core"; export const user = mssqlTable("user", { firstName: varchar('firstName', { length: 100 }), lastName: varchar('lastName', { length: 100 }), }, (table) => [ primaryKey({ columns: [table.firstName, table.lastName]}) ]); export const profile = mssqlTable("profile", { id: int("id").identity().primaryKey(), userFirstName: varchar("user_first_name", { length: 100 }), userLastName: varchar("user_last_name", { length: 100 }), }, (table) => [ foreignKey({ columns: [table.userFirstName, table.userLastName], foreignColumns: [user.firstName, user.lastName], name: "custom_name" }) ]); ```

Foreign key actions in MSSQL

Foreign key actions specify what happens when referenced data in the parent table is modified. Available actions: CASCADE (delete child rows when parent deleted), NO ACTION (default, prevent parent deletion if child exists), SET DEFAULT (set foreign key to default value), SET NULL (set foreign key to NULL). Defined with onDelete and onUpdate properties in references() second argument.

Foreign key action with references() method

Add foreign key actions using references() with a second argument: `int().references(() => users.id, { onDelete: 'cascade' })`. This sets up cascading delete behavior.

Give your agent this brain