Cascade + FK auto-index

reference() columns are foreign-key declarations. The framework picks two opinionated defaults that bite users from other frameworks:

export const posts = table('posts', {
  id:       id({ prefix: 'post' }),
  authorId: reference(() => users, {
    onDelete: 'cascade',         // delete author → delete posts. Default 'restrict'.
    onUpdate: 'noAction',        // FK PK never changes in practice. Default 'noAction'.
    index:    true,              // B-tree on authorId. Default true.
  }),
  title:    text(),
})

#onDelete defaults to 'restrict'

The framework is opinionated: deleting a user shouldn't silently nuke 100k posts. 'restrict' is the default to surface FK violations LOUDLY at delete time.

Value	Meaning
'restrict' (default)	Refuse the delete if child rows exist. Parent delete throws.
'cascade'	Delete child rows along with the parent. Use for owned lifecycles.
'setNull'	Set the child's FK to NULL (column must be .nullable()).
'noAction'	DB-level NO ACTION (postgres / mysql) — effectively same as restrict but defers the constraint check to commit.

Pick based on the lifecycle relationship between parent and child:

• 'cascade' for genuinely-owned children. Junction tables (memberships when a user OR org is deleted), child entities that have no meaning without the parent (a comment_reactions row when the comment is deleted).
• 'setNull' when the child outlives the parent in a degraded form. A posts.editorId when the editor user leaves the team — keep the post, drop the editor pointer.
• 'restrict' (default) when you want the framework to FORCE you to clean up explicitly. Most parent-child relationships in a typical app.

#index: true default

Every reference() column gets a B-tree index automatically. The framework's index audit confirms it at boot.

reference(() => users)              // → CREATE INDEX posts_author_id_idx ON posts (author_id);
reference(() => users, { index: false })  // → no index

The lookup cost of a missing FK index dwarfs the write cost of an unnecessary one for nearly every workload. Opt out only for tiny lookup tables where a full-scan beats index maintenance — lookup_codes (a 20-row enum-like table), singleton_config (one row total). Anywhere a JOIN would happen, keep the default.

#Real migration scenarios

#Scenario 1 — cascade through a junction

export const orgMemberships = table('org_memberships', {
  userId: reference(() => users, { onDelete: 'cascade' }),
  orgId:  reference(() => orgs,  { onDelete: 'cascade' }),
  role:   text(),
})

Delete a user → all their memberships cascade away. Delete an org → all its memberships cascade away. Standard pattern; the junction has no meaning without both parents.

#Scenario 2 — setNull on optional ownership

export const posts = table('posts', {
  authorId: reference(() => users, { onDelete: 'cascade' }),  // owner — cascade
  editorId: reference(() => users, { onDelete: 'setNull' }).nullable(),  // optional → setNull
})

If the editor leaves the team, posts stay but lose the editor pointer. The author's deletion still removes the post.

#Scenario 3 — restrict forcing explicit cleanup

export const invoices = table('invoices', {
  customerId: reference(() => customers, { onDelete: 'restrict' }),  // default
})

// Application code:
async function deleteCustomer(customerId: string) {
  const openInvoices = await store.query(
    database.invoices.where(eq('customerId', customerId)).descriptor,
  )
  if (openInvoices.length > 0) {
    throw new CustomerHasOpenInvoicesError({ count: openInvoices.length })
  }
  await ctx.store.delete('customers', customerId)
}

The 'restrict' default forces the application to think about what "delete customer" means. Auto-cascading invoices would corrupt accounting; auto-setNull would orphan them. The explicit check + typed error is the right contract.

#Per-dialect emission

Dialect	FK clause shape
postgres	REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE NO ACTION
mysql	REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION
mariadb	REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION
mssql	REFERENCES [users]([id]) ON DELETE CASCADE (mssql has no NO ACTION distinct from default)
sqlite	REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE NO ACTION

SQLite requires PRAGMA foreign_keys = ON to enforce — the framework sets this on every connect. Without it, sqlite is permissive (treats FK constraints as documentation only).

#onUpdate — usually 'noAction'

FK referenced columns are IDs. IDs don't change in practice — that's the whole point of having an ID. So onUpdate defaults to 'noAction' and you usually don't touch it.

If you DO need to change an ID across a hierarchy (e.g. consolidating two organizations into one — orgId: 'a' → orgId: 'b'), the right answer is usually to:

1. Find every row that references the old ID.
2. Update them all in a single transaction.
3. Delete the now-orphaned old parent row.

Not to set onUpdate: 'cascade' and trigger a single huge update through the DB engine. The cascade approach locks every affected table for the duration and breaks any subscriptions watching those tables.

#Composite FKs — not supported

The framework's reference() is single-column only. If your schema has a composite FK (multi-column referential integrity), you'll have to:

1. Declare each column individually with the right type.
2. Add the FK constraint via unsafe() SQL in a migration.
3. Skip the framework's relation declaration for that link.

This is rare. Composite FKs usually indicate a schema design that could collapse into a surrogate-key approach.

#Index audit warnings

The framework's boot-time index audit warns about redundant indexes — a FK auto-index that's a leading prefix of an explicit composite index is redundant:

[voltro:dev] WARN  index audit · redundant-prefix
  table:      memberships
  redundant:  memberships_user_id_idx       (from reference auto-index)
  coveredBy:  memberships_user_org_idx       (explicit composite on (user_id, org_id))
  hint:       drop one

Decide based on workload. If you frequently query by user_id alone, keep both. If the composite is the only access pattern, drop the auto-index via { index: false }.

#Caveats

• Cascade can chain deeply. A delete on users cascades to memberships, then to anything that references memberships with cascade, and so on. Trace the depth before you set 'cascade' on a high-cardinality table.
• Postgres DEFERRABLE INITIALLY DEFERRED FKs. The framework doesn't emit DEFERRED constraints. If you need them (mass-loading scenarios), emit them in a custom migration.
• MariaDB enforces FK constraints in storage engines that support them. InnoDB (the default) enforces; MyISAM doesn't. Use InnoDB. The framework's DDL emitter assumes it.
• SQLite enforcement requires PRAGMA foreign_keys = ON. The framework sets this at connect. Other clients that open the same database without setting the pragma bypass FK enforcement silently.

#Where it lives

• voltro/packages/database/src/columns.ts — reference() accepts onDelete / onUpdate / index
• voltro/packages/database/src/migrate.ts — referentialAction(action, dialect); the REFERENCES <table> (id) clause is emitted inline in the column-DDL builder alongside it
• voltro/packages/database/src/indexAudit.ts — boot-time redundant-prefix warnings