Databases · Lesson 2.1
Deleting a row others point at
So attempts' user ID is a foreign key, written with REFERENCES users (id) in the schema.
Ben's id is 2, and he has answered two questions.
So two rows in attempts, 3 and 6, hold user ID 2.
Each is a child row, and Ben's row in users is their parent row.
Now say Ben taps Delete account in Simplified's settings.
So Simplified's server sends a DELETE FROM users, where the id is 2.
If Postgres removed Ben's row, no row in users would have id 2.
Then attempts 3 and 6 would point at nobody, like an attempt with user ID 99.
And the foreign key promises that never happens.
So what does Postgres do with Ben's DELETE?
The foreign key is a constraint, so Postgres checks it on a DELETE too.
Before it removes Ben's row, Postgres looks in attempts for user ID 2.
It finds two child rows: attempts 3 and 6.
So Postgres refuses the delete, and sends back an error.
Nothing is deleted: Ben's row stays, and so do both of his attempts.
So by default, Postgres refuses to delete a parent row while child rows point at it.
But how does Postgres know to look in attempts?
Because the rule belongs to the foreign key.
It's written on attempts' user ID line in the schema: REFERENCES users (id).
That line makes Postgres check attempts before it deletes any learner.
Without it, user ID is just a whole number, and Ben's row would go.
And what if a learner has no attempts?
Cy's id is 3, and he hasn't answered a question yet.
So when Cy deletes his account, the server sends a DELETE FROM users, where the id is 3.
Postgres looks in attempts for user ID 3, and finds none.
No child row points at Cy, so Postgres removes his row.
It sends back DELETE 1: one row removed.
So how can Ben's account be deleted at all?
First, the server sends a DELETE FROM attempts, where the user ID is 2.
Attempts 3 and 6 pass, so Postgres removes both, and sends back DELETE 2.
Then the server sends the DELETE FROM users, where the id is 2, again.
Now nothing points at Ben's row, so it goes, and Postgres sends back DELETE 1.
That's two statements: the children first, then the parent.
Sent the other way round, the first one is refused, just as before.
So by default, a parent row can only go once no child row points at it.
Simplified's attempts table has user_id integer NOT NULL REFERENCES users (id), with no ON DELETE rule. Eli, whose id is 5, answers one question, so attempt 8 holds user_id 5. Then Eli deletes his account, and the server sends Postgres DELETE FROM users WHERE id = 5;.
What happens to Eli's row?
Explanation
First, Postgres looks in attempts for user_id 5, and finds attempt 8.
Then, a child row points at Eli's row, so by default Postgres refuses the delete and sends back an error.
Therefore, Eli's row stays, and so does attempt 8.
Simplified's users table holds 6 rows. Its attempts table has user_id integer NOT NULL REFERENCES users (id), with no ON DELETE rule, and Ben, whose id is 2, has two attempts. The server sends two statements, in this order: first DELETE FROM users WHERE id = 2;, then DELETE FROM attempts WHERE user_id = 2;.
How many rows does users hold after both?
Databases · Lesson 2.2
Deleting the child rows too
So deleting Ben's account took two statements: his attempts first, then his row.
His row alone was refused, because attempts 3 and 6 pointed at it.
But an attempt means nothing without its learner.
Once Ben's account is gone, nobody will ever look at his answers again.
So can Postgres delete Ben's attempts for the server, when Ben's row goes?
Yes, if attempts' schema says so, on the same user ID line.
After REFERENCES users (id), the line adds ON DELETE CASCADE.
ON DELETE CASCADE means: when a parent row is deleted, its child rows are deleted too.
So what does Ben's DELETE do now?
The server sends the same DELETE FROM users, where the id is 2.
Postgres finds Ben's row, and deletes it.
Then it looks in attempts for user ID 2, and finds attempts 3 and 6.
CASCADE is on that foreign key, so Postgres deletes both of them too.
That's 3 rows in all, from one statement, and nothing is refused.
The tag says DELETE 1, because it counts only the rows deleted from users.
So CASCADE does in one statement what took two.
So does every foreign key now cascade?
No: CASCADE is written on one foreign key's line, so it's chosen per foreign key.
attempts' lesson ID line has no ON DELETE, so it keeps the default.
Lesson 1 has four attempts, so deleting lesson 1 is still refused.
So how might Instagram delete your posts along with your account?
Say Instagram's posts table holds a user ID, with ON DELETE CASCADE.
Your account's id is 311, and you have three posts.
You delete your account, so Instagram's server might delete your row in users.
Postgres deletes it, then finds your three posts holding 311, and deletes them too.
So your posts go with your account: 4 rows, from one statement.
And what happens to a WhatsApp group's messages when the group is deleted?
Say WhatsApp keeps messages in a table, each holding a chat ID, with ON DELETE CASCADE.
Your group "Road trip" has the id 88, and four messages.
You delete the group, so WhatsApp's server might delete chat 88.
Postgres then deletes the four messages holding 88 too.
Your chat with Mom, id 42, keeps its message.
And what about the comments on a Google Doc?
Say Google Docs keeps comments in a table, each holding a doc ID, with ON DELETE CASCADE.
Your doc "Trip plan" has the id 7, and two comments from friends.
You delete the doc, so Google's server might delete doc 7.
Postgres deletes its two comments too, since a comment on no doc means nothing.
So CASCADE fits child rows that mean nothing without their parent.
One delete clears everything, so no child row is left pointing at nobody.
But you risk losing rows you meant to keep: one DELETE can take thousands with it.
Simplified's attempts table has user_id integer NOT NULL REFERENCES users (id) ON DELETE CASCADE, and no other table points at users. Fay, whose id is 6, has answered four questions, so four attempts hold user_id 6. Fay deletes her account, and the server sends Postgres DELETE FROM users WHERE id = 6;.
How many rows are deleted in all?
Explanation
First, Postgres deletes Fay's row from users: 1 row.
Then, CASCADE deletes the four attempts that hold user_id 6.
Therefore, 5 rows are deleted in all: .
Simplified's attempts table holds 7 rows: three with user_id 1, two with user_id 2, and two with user_id 4. Its user_id line is user_id integer NOT NULL REFERENCES users (id) ON DELETE CASCADE, and no other table points at users. The server sends Postgres DELETE FROM users WHERE id = 2;.
How many rows does attempts hold afterwards?
Simplified's attempts table has user_id integer NOT NULL REFERENCES users (id) ON DELETE CASCADE, and no other table points at users. Eli, whose id is 5, has answered six questions, so six attempts hold user_id 5. The server sends Postgres DELETE FROM users WHERE id = 5;.
How many rows does Postgres delete in all, across both tables?
Simplified's attempts table has user_id integer NOT NULL REFERENCES users (id) ON DELETE CASCADE and lesson_id integer NOT NULL REFERENCES lessons (id), with no ON DELETE on lesson_id. Lesson 1 has four attempts. The server sends Postgres DELETE FROM lessons WHERE id = 1;.
How many rows does Postgres delete?
Say Spotify keeps playlists in one table, and the songs added to each playlist in another, playlist_songs, where each row holds a playlist_id pointing at playlists' id. When you delete a playlist, its rows in playlist_songs should go too, in the same statement.
Which line should playlist_songs' schema have?
A team puts ON DELETE CASCADE on attempts' user_id line, which reads user_id integer NOT NULL REFERENCES users (id) ON DELETE CASCADE.
Which risk do they take on?
Simplified's attempts table has user_id integer NOT NULL REFERENCES users (id) ON DELETE CASCADE. Ben, whose id is 2, has attempts 3 and 6. The server sends Postgres DELETE FROM attempts WHERE id = 3;.
What happens to Ben's row in users?
Databases · Lesson 2.3
When a comment outlives its author
So with ON DELETE CASCADE, deleting Ben's row deleted his two attempts too.
That fits attempts, which mean nothing without their learner.
But on Reddit, other people reply to your comments.
Say Reddit keeps comments in a table, each holding its author's user ID.
Sam, whose id is 47, wrote comment 901: "Try a cache".
Maya replied with comment 902: "Thanks, that worked!"
And Sam answered that with comment 903: "Glad it helped".
Now say Sam deletes their account.
With CASCADE, comments 901 and 903 would go with it.
Then Maya's reply would answer a comment nobody can see.
So how can Sam's comments stay when Sam's account goes?
comments' schema can say ON DELETE SET NULL on its user ID line.
ON DELETE SET NULL means: a deleted parent's child rows stay, and their foreign key becomes NULL.
NULL is empty, so those comments no longer point at any user.
Its user ID line has no NOT NULL, so user ID can hold NULL.
So what does Sam's DELETE do now?
Reddit's server might send a DELETE FROM users, where the id is 47.
Postgres deletes Sam's row.
Then it looks in comments for user ID 47, and finds 901 and 903.
It keeps both comments, and puts NULL in their user ID.
So 1 row is deleted, and Maya's reply still has its comment.
So what does Reddit show as the author?
Comment 901's user ID is NULL, so there's no username to show.
So Reddit shows "deleted" where the username was.
And Maya's reply still sits under "Try a cache", so the thread still makes sense.
But what if user ID is NOT NULL?
Say comments' user ID line also said NOT NULL, as attempts' does.
Postgres still creates the table, with no error.
But when Sam's account is deleted, SET NULL tries to put NULL in user ID.
NOT NULL refuses that, so Postgres refuses the whole delete, and sends back an error.
Sam's row stays, and so do both of Sam's comments.
So SET NULL needs a foreign key column that allows NULL.
That's why Simplified's attempts, with user ID NOT NULL, can't use it.
So how might a member's messages stay in Slack after they leave?
Say Slack keeps messages in a table, with a user ID that allows NULL, and ON DELETE SET NULL.
Your teammate Jo, whose id is 64, posted two messages in the frontend channel.
Jo's account is deleted, so Slack's server might delete user 64.
Postgres keeps both messages, with NULL in their user ID.
So the channel still reads in full, and Slack might show a deactivated user as their author.
And what happens to a GitHub issue when its author deletes their account?
Say GitHub keeps issues in a table, with an author ID that allows NULL, and ON DELETE SET NULL.
Issue 12, "Button breaks on mobile", has author ID 77, and others are still fixing it.
Its author deletes their account, so GitHub's server might delete user 77.
Postgres keeps issue 12, with NULL in author ID, and GitHub might show "ghost" as its author.
So SET NULL fits child rows that others still need after the parent goes.
Every thread stays whole, so people keep reading and replying.
But you risk never knowing whose rows they were: the link to the parent is gone for good.
So on delete, a foreign key has three choices.
Refuse, the default: the parent can't go while children point at it.
CASCADE: the children go too.
SET NULL: the children stay, and forget their parent.
Each choice is written on the foreign key's own line, in the schema.
Say Reddit's comments table has user_id integer REFERENCES users (id) ON DELETE SET NULL, with no NOT NULL. maya_dev, whose id is 31, wrote comment 902, so it holds user_id 31. Maya deletes her account, and Reddit's server sends DELETE FROM users WHERE id = 31;.
What does comment 902's user_id hold afterwards?
Explanation
First, Postgres deletes Maya's row, and finds comment 902 holding user_id 31.
Then, SET NULL keeps comment 902, and empties its foreign key, which is allowed because user_id can hold NULL.
Therefore, comment 902's user_id holds NULL.
Say GitHub's issues table has author_id integer REFERENCES users (id) ON DELETE SET NULL, with no NOT NULL. Issue 30, "Login button misaligned", holds author_id 52. User 52 deletes their account, and GitHub's server sends DELETE FROM users WHERE id = 52;.
What happens to issue 30?
Say Slack's messages table has user_id integer REFERENCES users (id) ON DELETE SET NULL, with no NOT NULL. Its #general channel holds 12 messages, and 5 of them hold user_id 64. User 64's account is deleted with DELETE FROM users WHERE id = 64;.
How many messages does #general hold afterwards?
Say Reddit's comments table has user_id integer REFERENCES users (id) ON DELETE SET NULL, with no NOT NULL, and no other table points at users. User 47 wrote 8 comments. Reddit's server sends DELETE FROM users WHERE id = 47;.
How many rows does Postgres delete in all, across both tables?
Say a forum's comments table has user_id integer NOT NULL REFERENCES users (id) ON DELETE SET NULL. Postgres created the table with no error. User 47 has written 2 comments, and the forum's server sends DELETE FROM users WHERE id = 47;.
How many rows does Postgres delete?
Say Stack Overflow's answers table has a user_id pointing at users, and it allows NULL. Other people keep reading an answer, voting on it and commenting on it after its author deletes their account, so the answer should stay.
Which ON DELETE rule fits user_id?
A team writes user_id integer NOT NULL REFERENCES users (id) ON DELETE SET NULL in the CREATE TABLE for a comments table, then adds comments by user 12.
What happens?