Simplified
Simplified

L2.1Deleting a row others point at

YOU

Deleting a row others point at

CREATE TABLE attempts (id        integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,user_idinteger NOT NULL REFERENCES users (id),lesson_id integer NOT NULL REFERENCES lessons (id));
Delete accountDELETE FROM users WHERE id = 2;attemptsiduser_idlesson_id1112123214135416227418991usersidname1Ada2Ben3Cy4Dee5Eli6Faychild rowchild rowparent rowERROR: update or delete on table "users" violates foreign keyconstraint "attempts_user_id_fkey" on table "attempts"
CREATE TABLE attempts (id        integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,user_idinteger NOT NULL REFERENCES users (id),lesson_id integer NOT NULL REFERENCES lessons (id));
DELETE FROM users WHERE id = 3;attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6FayDELETE 1
1DELETE FROM attempts WHERE user_id = 2;DELETE 22DELETE FROM users WHERE id = 2;DELETE 1attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6Fay
DELETE FROM users WHERE id = 2;attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6FayERROR: update or delete on table "users" violates foreign keyconstraint "attempts_user_id_fkey" on table "attempts"

Databases · Lesson 2.1

Deleting a Parent Row

Deleting a row others point at

Simplified
0:00 / 2:37

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.

attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6Fay

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?

DELETE FROM users WHERE id = 2;attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6FayERROR: update or delete on table "users" violates foreign keyconstraint "attempts_user_id_fkey" on table "attempts"

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?

CREATE TABLE attempts (id        integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,user_id   integer NOT NULLREFERENCES users (id),lesson_id integer NOT NULL REFERENCES lessons (id));

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?

DELETE FROM users WHERE id = 3;attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6FayDELETE 1

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?

1DELETE FROM attempts WHERE user_id = 2;DELETE 22DELETE FROM users WHERE id = 2;DELETE 1attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6Fay12

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.

Example: Deleting a row others point at

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.

Question 1

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?

rows

Deleting the child rows too

1DELETE FROM attempts WHERE user_id = 2;DELETE 22DELETE FROM users WHERE id = 2;DELETE 1attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6Fay
DELETE FROM users WHERE id = 2;attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6FayERROR: update or delete on table "users" violates foreign keyconstraint "attempts_user_id_fkey" on table "attempts"
CREATE TABLE attempts (id        integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,user_id   integer NOT NULL REFERENCES users (id),lesson_id integer NOT NULL REFERENCES lessons (id));
ON DELETE CASCADE
when a parent row is deleted, its child rows are deleted too
DELETE FROM users WHERE id = 2;attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6FayON DELETE CASCADE123DELETE 1
CREATE TABLE attempts (id        integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,user_id   integer NOT NULL REFERENCES users (id) ON DELETE CASCADE,lesson_id integer NOT NULL REFERENCES lessons (id));
DELETE FROM lessons WHERE id = 1;attemptsiduser_idlesson_id111212321413541622741lessonsidtitle1What a database is2Tables3Reading dataERROR: update or delete on table "lessons" violates foreign keyconstraint "attempts_lesson_id_fkey" on table "attempts"
DELETE FROM users WHERE id = 311;postsiduser_idcaption80312New desk81311Beach day82311First app!83311Coffeeusersidusername311sam.codes312maya.devON DELETE CASCADE1234
DELETE FROM chats WHERE id = 88;messagesidchat_idtext70188Who's driving?70242Home soon70388I can!70488Leave at 8?70588Dealchatsidname42Mom88Road tripON DELETE CASCADE
DELETE FROM docs WHERE id = 7;commentsiddoc_idtext317Add a beach day?329Typo here337Love thisdocsidtitle7Trip plan9EssayON DELETE CASCADE
DELETE FROM users WHERE id = 2;attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6FayON DELETE CASCADE123DELETE 1

Databases · Lesson 2.2

Deleting a Parent Row

Deleting the child rows too

Simplified
0:00 / 2:55

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.

CREATE TABLE attempts (id        integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,user_id   integer NOT NULL REFERENCES users (id)ON DELETE CASCADE,lesson_id integer NOT NULL REFERENCES lessons (id));

So what does Ben's DELETE do now?

DELETE FROM users WHERE id = 2;attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6FayDELETE 1

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?

CREATE TABLE attempts (id        integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,user_id   integer NOT NULL REFERENCES users (id)ON DELETE CASCADE,lesson_id integer NOT NULLREFERENCES lessons (id));no ON DELETE

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.

DELETE FROM users WHERE id = 311;postsiduser_idcaption80312New desk81311Beach day82311First app!83311Coffeeusersidusername311sam.codes312maya.dev

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.

DELETE FROM chats WHERE id = 88;messagesidchat_idtext70188Who's driving?70242Home soon70388I can!70488Leave at 8?70588Dealchatsidname42Mom88Road trip

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.

DELETE FROM docs WHERE id = 7;commentsiddoc_idtext317Add a beach day?329Typo here337Love thisdocsidtitle7Trip plan9Essay

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.

Example: Deleting the child rows too

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: 1+4=51 + 4 = 51+4=5.

Question 1

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?

rows

Question 2

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?

rows

Question 3

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?

rows

Question 4

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?

Question 5

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?

Question 6

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?

When a comment outlives its author

DELETE FROM users WHERE id = 2;attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6FayON DELETE CASCADE123DELETE 1
sam_codes[deleted]Try a cachemaya_devThanks, that worked!
commentsiduser_idbody90147Try a cache90231Thanks, that worked!90347Glad it helpedusersidusername31maya_dev47sam_codesON DELETE CASCADE
CREATE TABLE comments (id      integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,user_id integer REFERENCES users (id) ON DELETE SET NULL,body    text NOT NULL);
ON DELETE SET NULL
a deleted parent's child rows stay, and their foreign key becomes NULL
CREATE TABLE comments (id      integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,user_idintegerREFERENCES users (id) ON DELETE SET NULL,body    text NOT NULL);no NOT NULL
DELETE FROM users WHERE id = 47;commentsiduser_idbody90147Try a cache90231Thanks, that worked!90347Glad it helpedusersidusername31maya_dev47sam_codesON DELETE SET NULLDELETE 1
sam_codes[deleted]Try a cachemaya_devThanks, that worked!
user_id integer NOT NULL REFERENCES users (id) ON DELETE SET NULL,DELETE FROM users WHERE id = 47;commentsiduser_idbody90147Try a cache90231Thanks, that worked!90347Glad it helpedusersidusername31maya_dev47sam_codesERROR: null value in column "user_id" of relation "comments"violates not-null constraint
CREATE TABLE attempts (id        integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,user_id   integerNOT NULLREFERENCES users (id),lesson_id integer NOT NULL REFERENCES lessons (id));
DELETE FROM users WHERE id = 64;messagesiduser_idtext50164PR is up50231Looks good50364Mergedusersidname31Maya64JoON DELETE SET NULL
DELETE FROM users WHERE id = 77;issuesidauthor_idtitle1152Add dark mode1277Button breaks on mobileusersidusername52lee-dev77cara-jsON DELETE SET NULL
DELETE FROM users WHERE id = 47;commentsiduser_idbody901—Try a cache90231Thanks, that worked!903—Glad it helpedusersidusername31maya_dev47sam_codesON DELETE SET NULLDELETE 1
refuse (the default)parentid 2childrenuser_id 2user_id 2ON DELETE CASCADEparentid 2childrenuser_id 2user_id 2ON DELETE SET NULLparentid 2childrenuser_id —user_id —

Databases · Lesson 2.3

Deleting a Parent Row

When a comment outlives its author

Simplified
0:00 / 3:49

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".

commentsiduser_idbody90147Try a cache90231Thanks, that worked!90347Glad it helpedusersidusername31maya_dev47sam_codes

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.

DELETE FROM users WHERE id = 47;commentsiduser_idbody90147Try a cache90231Thanks, that worked!90347Glad it helpedusersidusername31maya_dev47sam_codes

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.

CREATE TABLE comments (id      integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,user_id integer REFERENCES users (id)ON DELETE SET NULL,body    text NOT NULL);

So what does Sam's DELETE do now?

DELETE FROM users WHERE id = 47;commentsiduser_idbody901—Try a cache90231Thanks, that worked!903—Glad it helpedusersidusername31maya_dev47sam_codesDELETE 1

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?

sam_codes[deleted]Try a cachemaya_devThanks, that worked!

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.

user_id integerNOT NULLREFERENCES users (id) ON DELETE SET NULL,DELETE FROM users WHERE id = 47;commentsiduser_idbody90147Try a cache90231Thanks, that worked!90347Glad it helpedusersidusername31maya_dev47sam_codesERROR: null value in column "user_id" of relation "comments"violates not-null constraint

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.

DELETE FROM users WHERE id = 64;messagesiduser_idtext501—PR is up50231Looks good503—Mergedusersidname31Maya64Jo

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.

DELETE FROM users WHERE id = 77;issuesidauthor_idtitle1152Add dark mode12—Button breaks on mobileusersidusername52lee-dev77cara-js

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)parentid 2childrenuser_id 2user_id 2ON DELETE CASCADEparentid 2childrenuser_id 2user_id 2ON DELETE SET NULLparentid 2childrenuser_id —user_id —

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.

Example: When a comment outlives its author

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.

Question 1

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?

Question 2

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?

messages

Question 3

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?

rows

Question 4

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?

rows

Question 5

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?

Question 6

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?

Assistant

Highlight lesson text and click Ask AI, or type a question below.