Foreign keys and ON DELETE
Declare Drizzle foreign keys so Postgres enforces the links between tables, and pick the ON DELETE rule for each relationship.
Your invoiceLineItems table has an invoiceId column meant to point at a row in invoices, but it’s just a uuid that nothing enforces. You can insert a line item whose invoiceId matches no invoice, and when an invoice is deleted its line items linger, pointing at a row that no longer exists. A foreign key fixes both: it makes Postgres enforce the link on every write, and it lets you decide what happens to the children when the parent is deleted. That second part is a per-relationship choice between four options, and most of this lesson is about it.
A foreign key is a promise the database keeps
Section titled “A foreign key is a promise the database keeps”Here is the whole declaration, attached to the column it lives on:
organizationId: uuid().notNull().references(() => organizations.id, { onDelete: 'restrict' }),Three parts, left to right: the column, a uuid matching the type of the key it points at; .references(() => organizations.id, …), which names the target table and column; and the onDelete option, which sets the deletion policy. The next section covers onDelete, so ignore 'restrict' for now and focus on the link.
The reference is a callback, () => organizations.id, not organizations.id. When two tables reference each other, whichever module loads second hasn’t finished defining its tables, so reaching for the value directly throws a “cannot access before initialization” error. Wrapping it in a function defers the lookup until both tables exist.
Declaring it buys you three guarantees:
- Postgres rejects any insert or update whose
organizationIddoesn’t match an existingorganizations.id, so an orphan row can never be created. The check moves out of your application code: without the key, every path that inserts a line item must first confirm the invoice exists, and the one path that forgets ships the bug. - The foreign-key type must match the referenced primary key: a
uuidpoints at auuid, abigintat abigint. Get it wrong and the schema fails to apply up front, not at runtime. - The column is not indexed automatically. The constraint is a rule, not an index, so cascade deletes and “find every invoice for this org” lookups degrade to full-table scans as the table grows. Adding that index is the first thing we do in the indexing chapter.
For a key spanning multiple columns, use the table-level foreignKey({ columns, foreignColumns }) helper instead of .references(); since every table here keys on a single id, you’ll always want the single-column form.
This guarantee, that every foreign-key value points at a real row, is referential integrity , the floor every relational schema stands on.
The four ON DELETE rules, and a decision tree for picking one
Section titled “The four ON DELETE rules, and a decision tree for picking one”With the link in place, onDelete controls one moment: what Postgres does to the child rows when the parent they point at is deleted. The four rules, named by the relationship they fit:
cascade— delete the children too (a line item under an invoice).set null— keep the child, clear its pointer; requires a nullable column (an invoice’s assignee, who can be unassigned).restrict— block the delete while any child exists (an organization that still has invoices).set default— point the child at a column default. Know the name; you’ll almost never use it.
The right one falls out of the meaning of the relationship, so ask the questions in order: ownership, then blockability, then nullability.
The child is owned, worthless the moment the parent is gone, so it goes too. Example: invoiceLineItems.invoiceId → invoices.id. A cascade reaches far when the graph branches: deleting an org could sweep away its invoices, their line items, and their tags.
The parent delete throws while any child exists. Example: invoices.organizationId → organizations.id. The caller must reassign or remove the children first, or soft-delete the parent (the next section) so the delete never runs. Writing no onDelete clause doesn’t default to restrict; it defaults to NO ACTION , a near-twin that also blocks. The course writes restrict explicitly so the blocking intent is on the page.
Keep the child and clear its pointer, because the relationship is optional and orphaning is the goal. Example: invoices.assignedToId → users.id, where offboarding a user un-assigns their invoices instead of destroying them. This is why last lesson left assignedToId nullable. (set default is the rare sibling: point at a sentinel row instead of null.)
set null needs a column that can hold a null, and a .notNull() foreign key can’t. If the relationship is genuinely required, you want restrict or cascade, not set null. “Required plus set null” is a modeling mistake, not a configuration to fix.
Why onUpdate never fires here
Section titled “Why onUpdate never fires here”references() also takes an onUpdate policy that fires when the referenced primary-key value changes. Your primary keys are immutable surrogates (UUIDv7 or bigint identity), so they never change and onUpdate never fires. Reaching for it signals a mutable natural key, the anti-pattern last lesson steered you away from.
Hard delete vs. soft delete: which deletions actually happen
Section titled “Hard delete vs. soft delete: which deletions actually happen”cascade assumes the parent is hard-deleted, physically removed with a DELETE. In most web apps that’s the rare path.
The common path is soft delete: instead of removing the row, you stamp the nullable deletedAt column (already in your db/columns.ts) and let queries filter stamped rows out. The record is gone from the user’s view but still recoverable, auditable, and safe to reference historically. Because no DELETE runs, onDelete never fires.
So you choose per table. Reserve hard delete with cascade for true purges that must erase a whole relationship graph, such as a GDPR request or tenant offboarding. Use soft delete for almost everything user-facing, where “deleted” must stay recoverable. Declaring onDelete: 'cascade' doesn’t conflict with this: you’re defining what the rare purge does, not your everyday delete.
Wiring an invoice’s relationships end to end
Section titled “Wiring an invoice’s relationships end to end”Because the rule is per-edge, one entity can carry three different onDelete rules at once. Here are invoices and their line items, fully wired, with each step naming the edge, its rule, and why.
export const invoices = pgTable('invoices', { id: uuid().primaryKey().default(sql`uuidv7()`), organizationId: uuid() .notNull() .references(() => organizations.id, { onDelete: 'restrict' }), assignedToId: uuid() .references(() => users.id, { onDelete: 'set null' }), amountDue: numeric({ precision: 12, scale: 2 }).notNull(),});
export const invoiceLineItems = pgTable('invoice_line_items', { id: uuid().primaryKey().default(sql`uuidv7()`), invoiceId: uuid() .notNull() .references(() => invoices.id, { onDelete: 'cascade' }), description: text().notNull(), amount: numeric({ precision: 12, scale: 2 }).notNull(),});organizationId → organizations.id, restrict. The invoice means something without its org, and an org shouldn’t vanish under live invoices, so the delete is blocked. .notNull(), because every invoice belongs to an org.
export const invoices = pgTable('invoices', { id: uuid().primaryKey().default(sql`uuidv7()`), organizationId: uuid() .notNull() .references(() => organizations.id, { onDelete: 'restrict' }), assignedToId: uuid() .references(() => users.id, { onDelete: 'set null' }), amountDue: numeric({ precision: 12, scale: 2 }).notNull(),});
export const invoiceLineItems = pgTable('invoice_line_items', { id: uuid().primaryKey().default(sql`uuidv7()`), invoiceId: uuid() .notNull() .references(() => invoices.id, { onDelete: 'cascade' }), description: text().notNull(), amount: numeric({ precision: 12, scale: 2 }).notNull(),});assignedToId → users.id, set null. An optional pointer: deleting the user is allowed and the pointer just clears, so offboarding leaves the invoice unassigned, not deleted. No .notNull(), because set null needs a nullable column.
export const invoices = pgTable('invoices', { id: uuid().primaryKey().default(sql`uuidv7()`), organizationId: uuid() .notNull() .references(() => organizations.id, { onDelete: 'restrict' }), assignedToId: uuid() .references(() => users.id, { onDelete: 'set null' }), amountDue: numeric({ precision: 12, scale: 2 }).notNull(),});
export const invoiceLineItems = pgTable('invoice_line_items', { id: uuid().primaryKey().default(sql`uuidv7()`), invoiceId: uuid() .notNull() .references(() => invoices.id, { onDelete: 'cascade' }), description: text().notNull(), amount: numeric({ precision: 12, scale: 2 }).notNull(),});invoiceId → invoices.id, cascade. A line item is pure ownership, so deleting the invoice takes its items in one declarative rule instead of cleanup code. .notNull(), because a line item with no invoice is the orphan the foreign key exists to forbid.
That organizationId → organizations.id restrict repeats on every tenant-owned table, and its check scans invoices until the column is indexed.
Practice: enforce the relationships
Section titled “Practice: enforce the relationships”Wire the three foreign keys yourself. The starter has the tables, primary keys, and bare foreign-key columns; add .references(...) with the right onDelete to each. The requirements only check that a foreign key exists, so the probes verify the rules: deleting an org with invoices must be blocked, deleting an invoice must take its line items, and deleting an assigned user must leave the invoice with a nulled pointer.
Add the three foreign keys to invoices and invoice_line_items, each with the right onDelete rule. Derive the rule from the meaning of the relationship: an org with invoices must not be deletable, a line item is owned by its invoice, an assignee is an optional pointer that clears when the user goes. The requirements only check that each foreign key exists; the probes delete a parent and prove your rule fired, so a wrong rule turns one red.
What your schema produced
Reveal the answer
export const invoices = pgTable('invoices', { id: uuid('id').primaryKey(), organization_id: uuid('organization_id') .notNull() .references(() => organizations.id, { onDelete: 'restrict' }), assigned_to_id: uuid('assigned_to_id') .references(() => users.id, { onDelete: 'set null' }), amount_due: numeric('amount_due', { precision: 12, scale: 2 }).notNull(),});
export const invoiceLineItems = pgTable('invoice_line_items', { id: uuid('id').primaryKey(), invoice_id: uuid('invoice_id') .notNull() .references(() => invoices.id, { onDelete: 'cascade' }), description: text('description').notNull(), amount: numeric('amount', { precision: 12, scale: 2 }).notNull(),});Each rule matches its edge: organization_id is restrict (block the org delete, probe 1 throws), invoice_id is cascade (the line item goes with its invoice, probe 2 finds no orphan), and assigned_to_id is set null (offboarding clears the optional pointer, probe 3 finds the invoice intact with a null assignee).
External resources
Section titled “External resources”The reference pages for both ends of this lesson, Drizzle’s foreign-key API and the Postgres referential actions beneath it, are worth a bookmark. The two essays below argue the soft-delete call from opposite sides.
The references() API and the onDelete / onUpdate options, including the table-level multi-column form.
The ON DELETE action semantics straight from the source: cascade, set null, restrict, no action.
thoughtbot on the costs the lesson defers: query filtering, and the dependent-record trap where a soft-deleted parent hard-deletes its children.
The counter-argument: soft delete misleads the database, and lifecycle states or temporal tables often fit better. Read it to sharpen the call.