Constraints & Defaults

Constraints and defaults are declared on column builders or on the table. They are emitted as DDL when you generate migrations.

Primary key

Chain .pk(). A single .pk() column is the table’s primary key.

const users = defineTable("users", {
  id: int().pk(), // auto-increment integer PK
});

For a composite key, set it at the table level instead:

defineTable("memberships", { userId: int(), teamId: int() }, {
  primaryKey: ["userId", "teamId"],
});

Not null

Columns are nullable unless you chain .notNull():

email: text().notNull(),

Unique

Chain .unique() on a column, or declare a multi-column unique group:

// single column
email: text().notNull().unique(),

// composite group (table option)
defineTable("memberships", { userId: int(), teamId: int() }, {
  unique: [["userId", "teamId"]],
});

Foreign key

Chain .references(targetTable, targetColumn, opts?):

ownerId: int().notNull().references("users", "id", { onDelete: "CASCADE" }),

Options:

Option	Effect
onDelete	CASCADE, SET NULL, RESTRICT, …
onUpdate	same semantics on update

Check

Chain .check(expr) for a column-level CHECK:

age: int().check("age >= 0"),

Table-level checks go in the options:

defineTable("accounts", { balance: real() }, { checks: ["balance >= 0"] });

Defaults

Use .default(value) for a literal, or .defaultNow() for CURRENT_TIMESTAMP:

active: bool().notNull().default(true),
createdAt: timestamp().notNull().defaultNow(),

SQL-level, not JS-level

defaultNow() emits CURRENT_TIMESTAMP in the DDL — the database sets it, not your app. .default(true) stores the literal 1 (booleans are 0/1).

Use .onUpdate(expr) to set a column on every update:

updatedAt: timestamp().notNull().defaultNow().onUpdate(),

Best practices

• Put referential integrity in .references() so migrations create the FK.
• Prefer defaultNow() over setting timestamps in app code.
• Use composite unique groups for join tables.

Common mistakes

• Forgetting .notNull() on a FK column — it becomes nullable.
• Using .default(new Date()) (JS value) when you meant .defaultNow().

Related

• Column Types — type mappings behind each column.
• Migrations → Generate — how constraints become DDL.