Foreign keys and ON DELETE

Right now your invoiceLineItems table has an invoiceId column that is supposed to point at a row in invoices, but it’s just a uuid, and nothing enforces that promise. You could insert a line item whose invoiceId matches no invoice at all, and when an invoice is deleted, its line items sit there pointing at a row that no longer exists. A foreign key fixes both problems: it makes Postgres enforce the link on every write, and it lets you decide what happens to the children when the parent is deleted. Most of this lesson goes into that second part, which is a four-way choice you make per relationship, plus a look at when deletions actually happen. Last lesson ended on “every foreign key points at exactly one primary key”, and this lesson covers the foreign-key end of that sentence.

A foreign key is a promise the database keeps

Before we touch deletion behavior, let’s get the foreign key working as a pure guarantee. Here is the whole declaration, attached to the column it lives on:

db/schema.ts

organizationId: uuid().notNull().references(() => organizations.id, { onDelete: 'restrict' }),

There are three pieces, left to right. First, the column itself, a uuid matching the type of the primary key it points at. Second, the .references(() => organizations.id, …) call, which names the target table and column. Third, the onDelete option, which sets the deletion policy. Ignore onDelete: 'restrict' for one more section, since the next section is entirely about what those values mean. For now, just focus on the link itself.

Notice the reference is a callback: () => organizations.id, not organizations.id. That arrow is there for a reason. When two tables reference each other, as they will in a real schema, whichever module loads second hasn’t finished defining its tables yet, so reaching for the value directly throws a “cannot access before initialization” error. Wrapping it in a function defers that lookup until both tables exist, and Drizzle asks for the callback precisely so you never hit that error.

So what does declaring this actually buy you? Three things:

1. Postgres rejects any write, insert or update, whose organizationId doesn’t match an existing organizations.id. An orphan row is now impossible to create. The database refuses it at the boundary.
2. The column types must match the referenced primary key exactly: a uuid foreign key points at a uuid primary key, a bigint at a bigint. Get this wrong and the schema fails to apply, so it’s caught up front rather than as a runtime surprise. This is why last lesson’s primary-key shapes matter here: the foreign key inherits the type of whatever it points at.
3. The foreign-key column is not indexed automatically. Declaring the constraint creates the rule, not an index. Without one, both the cascade deletes you’re about to meet and ordinary “find every invoice for this org” lookups degrade to full-table scans as the table grows. Keep that in mind for now; adding the index is the first thing we do in the indexing chapter.

All three add up to one payoff: the foreign key moves orphan-prevention out of your application code and down to the database boundary. Without it, every code path that inserts a line item has to first check “does this invoice exist?”, and the one path that forgets to check ships the bug. With it, the question is unnecessary, because a bad write would have failed before your code ran. The next lesson extends this same idea to uniqueness and value checks: the database is the last line of defense, so it’s not something you can skip.

There’s one shape you’ll see named but rarely write: a foreign key that spans multiple columns uses the table-level foreignKey({ columns, foreignColumns }) helper instead of .references(). Because every table in this course keys on a single surrogate id, single-column foreign keys are almost always the shape you want. Recognize the multi-column form, but don’t reach for it.

This guarantee, that every foreign-key value points at a real row, is referential integrity , and it’s the floor every relational schema stands on.

Choosing what happens on delete: the four ON DELETE rules

With the link in place, you can make the deletion decision. onDelete controls one specific moment: what Postgres does to the child rows when the parent row they point at gets deleted. There are four possible answers, and choosing between them is a modeling call. The right answer comes from the meaning of the relationship, not from a default you accept without looking.

• cascade deletes the children too. Reach for it when the child has no meaning without its parent: a line item under an invoice, a junction row under either table it joins. This is the ownership relationship. The test is simple: if the parent is gone, is the child now worthless? If so, cascade.

• set null keeps the child but nulls out its pointer. Reach for it when the relationship is optional and orphaning is exactly what you want: an invoice’s assignedToId when the assigned user is offboarded. The invoice survives, now unassigned. This requires the column to be nullable (no .notNull()). Calling set null on a .notNull() column is a contradiction that Postgres will reject, since the rule’s whole job is to write a null that the column forbids. This is why last lesson’s assignedToId was left nullable.

• restrict blocks the delete while any child still exists. Reach for it when removing the parent out from under live children would be a mistake the system should refuse: an organization that still has invoices. The delete throws, and whoever wanted it gone must remove or reassign the children first. Far more often, you soft-delete the parent instead, which is the next section. One subtlety is easy to get wrong, so it’s worth spelling out: if you write no onDelete clause at all, Postgres doesn’t fall back to restrict. It falls back to NO ACTION , a near-twin that also blocks but defers its check to the end of the transaction rather than checking immediately per statement. The course always writes restrict explicitly, so the blocking intent is on the page rather than inferred from an omission. The deferred-versus-immediate difference between the two is out of scope.

• set default sets the child’s pointer to a column default. It exists for completeness, but it needs a sentinel default row to point at and rarely earns that wiring. Know the name; you’ll almost never use it.

Those are the four options. The harder skill is the order you ask the questions in, because the rule falls out of the answers. Walk this decision tree: each branch commits to the next question, and the leaf names the rule plus its canonical example.

Which onDelete rule does this relationship want?

What matters most is that question order, ownership then blockability then nullability, rather than any single leaf. Memorizing a lookup table of four rules ages badly. Internalizing the order means you can derive the right rule for a relationship you’ve never seen before.

onUpdate, and why this course never needs it

references() takes a second policy alongside onDelete: an onUpdate rule that fires when the referenced primary-key value itself changes. In this course it has no work to do. Primary keys here are immutable surrogates, UUIDv7 or bigint identity, and they never change once minted, so onUpdate has nothing to fire on. The only place it earns its keep is mutable natural keys, which is exactly the anti-pattern last lesson steered you away from. Treat it as a non-decision: if you ever find yourself reaching for onUpdate, that’s a signal your primary key should have been a surrogate, not a value that drifts.

Hard delete vs. soft delete: which deletions even happen

Cascade quietly assumes something you should question: that the parent is hard-deleted, the row physically removed with a DELETE statement. In a lot of SaaS, that’s the rare path.

The common path is soft delete. Instead of removing the row, you stamp it with a deletedAt timestamp, the nullable column already sitting in your db/columns.ts from the column-modifiers lesson. The row stays in the table, and your queries learn to filter out the stamped ones. The record is then gone from the user’s point of view but still recoverable, auditable, and present for anything that references it historically. This is the part that reframes everything you just learned: because the row is never DELETEd, onDelete never fires. On the soft-delete path, cascade and restrict are simply irrelevant, because they’re rules about a statement you’re not running.

So the senior split is a per-table decision about which kind of deletion a table even gets:

• Hard delete with cascade for genuinely scrubbing a relationship graph: tenant offboarding, where an org and everything it owns must be permanently erased (a GDPR deletion request, say), or ephemeral owned children where no audit trail matters.
• Soft delete for almost everything user-facing: invoices, customers, anything where “deleted” has to be recoverable or referenced later. Here onDelete is a backstop for the rare true purge, not the everyday path.

The point people miss is that declaring onDelete: 'cascade' and soft-deleting are not in conflict. The foreign-key rule defines what happens if a hard delete ever runs, whether that’s a purge job, an admin action, or a GDPR erase. Your normal application flow soft-deletes, so the rule simply doesn’t trigger. You still choose onDelete deliberately for every foreign key, even in a soft-delete-first app, because you’re choosing what the rare purge does, not what your everyday delete does.

Note

Choose onDelete for the purge that might happen; choose soft delete for the deletes that actually happen day to day.

Watch video

Derek Comartin (CodeOpinion) reframes the choice in 7 minutes: why he models terminations and events rather than flipping an isDeleted flag, and how foreign-key constraints stop a hard delete in the first place.

The mechanics of soft delete all belong to the soft-delete chapter later in the course: filtering deletedAt out of every query, the partial-unique trick that lets a name be reused after deletion, and the restore-and-archive lifecycle. The job here is narrower: keep cascade in its lane so you don’t reach for it as your default “delete” button.

Wiring an invoice’s relationships end to end

Now you can put the decision to work on real tables. The point of this example is that one entity can legitimately carry three different onDelete rules at once, because the rule is per-edge, not per-table. Here’s invoices and its line items with every foreign key wired. Each step below lands on one foreign key, its rule, and the one question from the walker that picked it.

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 walker’s first question: does an invoice mean anything without its org? Yes, so cascade is wrong. Second question: should you be able to delete an org with live invoices? No. The org gets blocked from deletion, and is soft-deleted in practice anyway. It’s .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. This is an optional pointer: an invoice can stand alone, deleting the user should be allowed, and the pointer should just clear. Note the missing .notNull(), since set null requires a nullable column, which is exactly why this column was left nullable last lesson. Offboard a user and their invoices go unassigned, not deleted.

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 meaningless without its invoice, which is pure ownership, so the first question alone settles it. Delete the invoice and its line items go with it, in one declarative rule instead of cleanup code in every delete path. It’s .notNull(), because a line item with no invoice is the orphan the foreign key exists to forbid.

← Previous 1 / 1 Next →

One table, three rules: restrict, set null, and cascade, each correct for its own edge.

Two more threads are worth pulling, briefly. There’s a fourth relationship coming: invoices and tags are many-to-many, joined through an invoiceTags table whose two foreign keys both cascade, because a junction row is owned by both endpoints, so it should vanish when either one goes. Building that junction is the next lesson’s job; just note the cascade-on-both for now. Notice organizationId again, too: every tenant-owned table will carry exactly this restrict foreign key to organizations. It’s where multi-tenancy starts in the schema. Actually enforcing that an org only ever sees its own rows is a query-layer concern much later in the course. For now, the foreign key is the seed.

One last reminder, since it matters here specifically: deleting an org has to scan invoices for matching organizationId rows to apply that restrict check, and with no index on the column, that’s a full-table scan. The indexing chapter fixes it; the issue is just easier to picture now that you can see the relationship it slows down.

Practice: enforce the relationships

Wire the three foreign keys yourself. The starter has the tables and their primary keys; the foreign-key columns are present but bare. Add .references(...) to each with the right onDelete rule. The requirements can only check that a foreign key exists, so the rules themselves are verified by the probes underneath: one deletes an org that has invoices (which must be blocked), one deletes an invoice that has line items (the items must vanish with it), and one deletes an assigned user (the invoice must survive with its pointer nulled). Get the rule wrong and the matching probe fails.

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 can only check that each foreign key exists; the probes underneath delete a parent and prove your rule fired, so a wrong rule turns one of them red.

Check schema Feedback Reset

Reveal the answer

db/schema.ts

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(),
});

Walk the rules back to the walker’s questions. organization_id → organizations.id is restrict: an invoice means something without its org, and you should not be able to delete an org with live invoices, so the delete is blocked (probe 1 throws, as it must). invoice_id → invoices.id is cascade: a line item is owned by its invoice, so it goes when the invoice goes (probe 2 deletes the invoice and finds no orphaned line item). assigned_to_id → users.id is set null: the pointer is optional, so offboarding the user clears it instead of deleting or blocking the invoice (probe 3 deletes the user and finds the invoice intact with a null assignee), which only works because the column is nullable.

External resources

The reference pages for both ends of what you just learned, Drizzle’s foreign-key API and the underlying Postgres referential actions, are worth a bookmark for the day you need a rule the lesson didn’t cover. The two essays below push on the soft-delete call from opposite sides, which is exactly the senior judgment this lesson asks you to develop.

Drizzle ORM — Foreign keys

orm.drizzle.team

The references() API and the onDelete / onUpdate options, including the table-level multi-column form.

PostgreSQL — Foreign key constraints

postgresql.org

The ON DELETE action semantics straight from the source: cascade, set null, restrict, no action.

The hard truth about soft deletion

thoughtbot.com

thoughtbot on the costs the lesson defers: query filtering, and the dependent-record trap where a soft-deleted parent hard-deletes its children.

Avoiding the soft delete anti-pattern

cultured.systems

The counter-argument: soft delete misleads the database, and lifecycle states or temporal tables often fit better. Read it to sharpen the call.