cockroach/constraints

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

Multi-column foreign key example

To create a foreign key referencing multiple columns, use the foreignKey() operator with arrays: foreignKey({ columns: [table.userFirstName, table.userLastName], foreignColumns: [user.firstName, user.lastName], name: 'custom_fk' })

Setting column defaults with .default() and .defaultRandom()

Use .default(42) to set a constant default value for a column. Use .default(sql`24`) to set a default from a SQL expression. Use .defaultRandom() for UUID columns to generate a random UUID as default. Example: int4().default(42), int4().default(sql`24`), uuid().defaultRandom(), uuid().default(sql`gen_random_uuid()`)

NOT NULL constraint prevents NULL values

The NOT NULL constraint enforces that a column cannot accept NULL values. This means every record must have a value in this field; you cannot insert or update a record without providing a value. Use .notNull() on a column definition to apply this constraint.

Declaring UNIQUE constraints on columns

For a single column UNIQUE constraint, use .unique() on the column definition. To give the constraint a custom name, use .unique('custom_name'). Example: id: int4().unique() or id: int4().unique('custom_name')

Composite UNIQUE constraints on multiple columns

To create a UNIQUE constraint on multiple columns, use the unique() function in the table definition callback. Use unique().on(t.id, t.name) for an unnamed composite constraint or unique('custom_name').on(t.id, t.name) for a named constraint. Example: in the table callback, pass [unique().on(t.id, t.name), unique('custom_name').on(t.id, t.name)]

CHECK constraint limits value range in column

The CHECK constraint limits the value range that can be placed in a column. When defined on a column, it allows only certain values. When defined on a table, it can limit values in certain columns based on values in other columns in the row.

Declaring CHECK constraints using check() function

Use the check() function in the table definition callback to declare CHECK constraints. Syntax: check('constraint_name', sql`${table.column} > value`). Example: check('age_check1', sql`${table.age} > 21`) generates CONSTRAINT "age_check1" CHECK ("users"."age" > 21)

Declaring single-column PRIMARY KEY

To declare a primary key on a single column, use .primaryKey() on the column definition. Example: id: int4('id').primaryKey() or id: string('cuid').primaryKey()

Composite PRIMARY KEY with multiple columns

A composite primary key uniquely identifies each record using multiple fields. Use the standalone primaryKey() operator in the table definition callback. Syntax: primaryKey({ columns: [table.col1, table.col2] }) for unnamed or primaryKey({ name: 'custom_name', columns: [table.col1, table.col2] }) for named.

Declaring foreign key in column definition

Use .references(() => parentTable.id) on a column definition to declare a foreign key. Example: authorId: int4('author_id').references(() => user.id) in the book table references the user table's id column.

Self-referencing foreign key with type annotation

For self-referencing foreign keys, due to TypeScript limitations, explicitly set the return type for the reference callback using AnyCockroachColumn. Example: parentId: int4('parent_id').references((): AnyCockroachColumn => user.id)

Foreign key operator for custom naming and multi-column references

Use the standalone foreignKey() operator in the table definition callback for custom foreign key naming or multi-column foreign keys. Syntax: foreignKey({ columns: [table.col1, ...], foreignColumns: [parentTable.col1, ...], name: 'custom_fk' })

PostgreSQL unique constraint in Drizzle

To create a unique constraint on a PostgreSQL column in Drizzle, chain .unique() to the column definition. Example: p.text().notNull().unique()

Foreign key with cascade delete in Postgres

Define a foreign key constraint with cascade delete using .references() with options { onDelete: 'cascade' }. For example: .references(() => usersTable.id, { onDelete: 'cascade' }).

Give your agent this brain