0
Challenge · 20SQL III: Relationships & Joins
Foreign keys & ON DELETE actions
Schemaused in ~95% of apps
A junior sees `posts.user_id` holding the same value as `profiles.id` and calls the relationship "done" — the app joins them, the app displays them, it works. Then someone deletes a test account, and 40,000 posts start pointing at a user_id that no longer exists. Nothing crashed at delete time; the crash happens three weeks later, in production, the first time code assumes every post has a real author. A foreign key is not decoration on top of a working relationship. Without ON DELETE RESTRICT, ON DELETE CASCADE, or ON DELETE SET NULL — chosen on purpose — that delete either silently corrupts your data or silently destroys more of it than you meant to.
Core idea. A FOREIGN KEY constraint tells Postgres "every value in this column must exist in that other table's key column, or the write is rejected." That is the whole mechanism — it turns a modeling assumption into something the database enforces on every INSERT and UPDATE. But a constraint that only talks about writing rows says nothing about deleting the row being pointed at. ON DELETE is the second half of the sentence: when the parent row disappears, do its children get deleted too (CASCADE), block the deletion (RESTRICT), or get their pointer blanked (SET NULL)? Postgres will not guess. Omit the clause and you get the strictest of the three by default — reject the delete — which is often not what the product needs.
poke it, drag it,
try to break it
try to break it
no slides here.
you drive.
you drive.
Playground, this isn't a screenshot
posts.user_id → profiles.id
user_id uuid not null
references profiles(id) on delete restrictprofiles
alice
posts
Why I switched to Postgres
post #1
Draft: RLS notes
post #2
“An account cannot be deleted while it still owns posts — deletion of content must be deliberate, never a side effect of deleting the account.”
↑ play a little first, then dig in