Simplified
Simplified

L1.1Rules Postgres checks on every write

YOU

Rules Postgres checks on every write

email text NOT NULL,usersidemailnamexp1ada@simplified.orgAda1202ben@simplified.orgBen403cy@simplified.orgCy04dee@simplified.orgDee3005eli@simplified.orgEli406fay@simplified.orgFay757you@simplified.orgYou08—Hal0INSERTINSERT
attemptsiduser_idlesson_id1112123214135416227418739991INSERTINSERTusersidname1Ada2Ben3Cy4Dee5Eli6Fay7Youuser_id integer NOT NULLREFERENCES users (id),
usersidemailname1ada@simplified.orgAda2ben@simplified.orgBen3cy@simplified.orgCy4dee@simplified.orgDee5eli@simplified.orgEli6fay@simplified.orgFay7you@simplified.orgYou8—HalINSERTattemptsiduser_idlesson_id1112123214135416227418739991INSERT
UPDATE users SET email = NULLWHERE id = 1;usersidemailnamexp1ada@simplified.orgAda1202ben@simplified.orgBen403cy@simplified.orgCy04dee@simplified.orgDee3005eli@simplified.orgEli406fay@simplified.orgFay75UPDATENULL?email text NOT NULL,ERROR: null value in column "email" ofrelation "users" violates not-nullconstraint
constraints
rules in a table's schema that every row must follow
INSERTUPDATEemail textNOT NULL,ERROR
CREATE TABLE users (id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,email text NOT NULL,name varchar(50),xp integer NOT NULL DEFAULT 0,created_at timestamp NOT NULL DEFAULT now());1CREATE 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));2
UPDATE attempts SET user_id = 99WHERE id = 3;attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6Fay99?ERROR: insert or updateon table "attempts"violates foreign keyconstraint"attempts_user_id_fkey"
CREATE TABLE users (id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,email text NOT NULL,name varchar(50),xp integer NOT NULL DEFAULT 0,created_at timestamp NOT NULL DEFAULT now());?
INSERT INTO users (email, name)VALUES ('gia@simplified.org', 'Gia');usersidemailnamexp1ada@simplified.orgAda1202ben@simplified.orgBen403cy@simplified.orgCy04dee@simplified.orgDee3005eli@simplified.orgEli406fay@simplified.orgFay757gia@simplified.orgGia0INSERT 0 1
constraintsNOT NULLREFERENCES users (id)DEFAULT 0

Databases · Lesson 1.1

Unique Values

Rules Postgres checks on every write

Simplified
0:00 / 2:39

When you sign up to Simplified, its server sends an INSERT, and Postgres adds your row to users.

Say a sign-up came with no email.

users' email column has NOT NULL, so Postgres refused that row, and stored nothing.

And when you answer a question, the server sends an INSERT into attempts.

Say a bug sent an attempt with user ID 99.

User ID is a foreign key, and no row in users has id 99, so Postgres refused that too.

INSERT INTO attempts (user_id, lesson_id)VALUES (99, 2);attemptsiduser_idlesson_id111212321413541622741—992usersidname1Ada2Ben3Cy4Dee5Eli6Fay

So both times, Postgres refused a new row, sent with an INSERT.

But Simplified's server also changes rows that are already there, with UPDATE.

Say Ada clears the email in her settings, and a bug sends it on as NULL.

So does NOT NULL also stop a row from changing into one with no email?

UPDATE users SET email = NULLWHERE id = 1;usersidemailnamexp1ada@simplified.orgAda1202ben@simplified.orgBen403cy@simplified.orgCy04dee@simplified.orgDee3005eli@simplified.orgEli406fay@simplified.orgFay75NULLERROR: null value in column "email" of relation "users" violates not-nullconstraint

So the server sends an UPDATE that sets the email in Ada's row, id 1, to NULL.

Before changing Ada's row, Postgres sees that email has NOT NULL, and the new value is NULL.

So Postgres refuses the UPDATE, and sends back an error instead of UPDATE 1.

Nothing changes: Ada's row still holds ada@simplified.org, so she can still log in.

So Postgres checks NOT NULL when a row changes, just as when one is added.

Rules like this are called constraints.

A constraint is a rule in a table's schema that every row must follow.

Postgres checks it each time a row is added or changed, and refuses any change that breaks it.

1CREATE TABLE users (  id         integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,  email      text NOT NULL,  name       varchar(50),  xp         integer NOT NULL DEFAULT 0,  created_at timestamp NOT NULL DEFAULT now());2CREATE 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));

You've already met two, each written in a table's schema.

The first is NOT NULL on users' email.

The second is the foreign key on attempts' user ID.

So what if an UPDATE moves an attempt to a learner who doesn't exist?

UPDATE attempts SET user_id = 99WHERE id = 3;attemptsiduser_idlesson_id11121232141354162274199ERROR: insert or update on table "attempts" violates foreign key constraint"attempts_user_id_fkey"

Attempt 3 is one of Ben's answers, so its user ID is 2.

Say a bug sends an UPDATE that sets attempt 3's user ID to 99.

Before changing the row, Postgres looks in users for a row whose id is 99.

No row has it, so the foreign key refuses the UPDATE.

Attempt 3 still holds 2, so it still counts as Ben's answer.

So is DEFAULT a constraint too?

DEFAULT is a rule in users' schema as well, like xp's DEFAULT of 0.

INSERT INTO users (email, name)VALUES ('gia@simplified.org', 'Gia');usersidemailnamexp1ada@simplified.orgAda1202ben@simplified.orgBen403cy@simplified.orgCy04dee@simplified.orgDee3005eli@simplified.orgEli406fay@simplified.orgFay757gia@simplified.orgGia0INSERT 0 1

Say Gia signs up, and her INSERT leaves out xp.

So Postgres fills in xp's DEFAULT, 0, and adds her row.

A DEFAULT only fills in a value. It never refuses a write.

So DEFAULT isn't a constraint: a constraint is a rule that can refuse.

So a constraint is a schema rule Postgres checks on every write, refusing any write that breaks it.

Example: Rules Postgres checks on every write

Simplified's attempts table has a foreign key on user_id, REFERENCES users (id), and users holds the ids 1 to 6. Attempt 5 holds user_id 4. Simplified's server sends Postgres UPDATE attempts SET user_id = 7 WHERE id = 5;.

What does attempt 5's user_id hold afterwards?

Explanation

First, the foreign key is a constraint, so Postgres checks it on this UPDATE too.

Then, no row in users has id 7, so Postgres refuses the UPDATE, and nothing changes.

Therefore, attempt 5's user_id still holds 4.

Question 1

Simplified's schemas hold these four rules. Postgres refuses a write that breaks a constraint.

Which rule is not a constraint?

Two sign-ups, one email

email textNOT NULL,usersidemailnamexp1ada@simplified.orgAda1202ben@simplified.orgBen403cy@simplified.orgCy04dee@simplified.orgDee3005eli@simplified.orgEli406fay@simplified.orgFay75
INSERT INTO users (email, name)VALUES ('ada@simplified.org', 'Ada');usersidemailnamexp1ada@simplified.orgAda1202ben@simplified.orgBen403cy@simplified.orgCy04dee@simplified.orgDee3005eli@simplified.orgEli406fay@simplified.orgFay757ada@simplified.orgAda0email textNOT NULL,INSERT 0 1?
CREATE TABLE users (id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,email text NOT NULL,name varchar(50),xp integer NOT NULL DEFAULT 0,created_at timestamp NOT NULL DEFAULT now());?INSERTUPDATE
UNIQUE
no two rows may hold the same value in that column
CREATE TABLE users (id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,email text NOT NULLUNIQUE,name varchar(50),xp integer NOT NULL DEFAULT 0,created_at timestamp NOT NULL DEFAULT now());?INSERTUPDATE
INSERT INTO users (email, name)VALUES ('ada@simplified.org', 'Ada');usersidemailnamexp1ada@simplified.orgAda1202ben@simplified.orgBen403cy@simplified.orgCy04dee@simplified.orgDee3005eli@simplified.orgEli406fay@simplified.orgFay757ada@simplified.orgAda0email text NOT NULLUNIQUE,ERROR: duplicate key value violatesunique constraint "users_email_key"?
UPDATE users SET email = 'ada@simplified.org'WHERE id = 2;usersidemailnamexp1ada@simplified.orgAda1202ben@simplified.orgBen403cy@simplified.orgCy04dee@simplified.orgDee3005eli@simplified.orgEli406fay@simplified.orgFay75ada@simplified.org?email text NOT NULLUNIQUE,ERROR: duplicate key value violatesunique constraint "users_email_key"
InstagramUsernamemaya.devThat username is takenusername text UNIQUEaccountsidusername311sam.codes312maya.dev313leo.k314maya.dev
email text NOT NULLUNIQUE,usersidemailnamexp1ada@simplified.orgAda1202ben@simplified.orgBen403cy@simplified.orgCy04dee@simplified.orgDee3005eli@simplified.orgEli406fay@simplified.orgFay75

Databases · Lesson 1.2

Unique Values

Two sign-ups, one email

Simplified
0:00 / 2:25

So users' email has NOT NULL, a constraint, so every row must hold an email.

Ada logs in with hers, ada@simplified.org, users' logical key.

But NOT NULL only checks that an email is there, not that it's new.

Say Ada forgets she has an account, and signs up again with ada@simplified.org.

So Simplified's server sends an INSERT with that email, and the name Ada.

INSERT INTO users (email, name)VALUES ('ada@simplified.org', 'Ada');usersidemailnamexp1ada@simplified.orgAda1202ben@simplified.orgBen403cy@simplified.orgCy04dee@simplified.orgDee3005eli@simplified.orgEli406fay@simplified.orgFay757ada@simplified.orgAda0INSERT 0 1

NOT NULL is met: the new row has an email.

So Postgres adds it as row 7, with xp's DEFAULT, 0, and sends back INSERT 0 1.

Now two rows hold ada@simplified.org: two accounts behind one login.

When Ada logs in, two rows match her email, with 120 XP and 0. Which XP does she see?

So how can Postgres stop a second row from taking Ada's email?

users' schema adds one more word to email's line, after NOT NULL: UNIQUE.

UNIQUE means no two rows may hold the same value in that column.

It's a constraint, so Postgres checks it on every INSERT and every UPDATE.

CREATE TABLE users (  id         integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,  email      text NOT NULL UNIQUE,  name       varchar(50),  xp         integer NOT NULL DEFAULT 0,  created_at timestamp NOT NULL DEFAULT now());

So what happens to Ada's second sign-up now?

INSERT INTO users (email, name)VALUES ('ada@simplified.org', 'Ada');usersidemailnamexp1ada@simplified.orgAda1202ben@simplified.orgBen403cy@simplified.orgCy04dee@simplified.orgDee3005eli@simplified.orgEli406fay@simplified.orgFay757ada@simplified.orgAda0ERROR: duplicate key value violates unique constraint "users_email_key"

The server sends the same INSERT, with ada@simplified.org.

Before storing it, Postgres looks in the email column for that value.

Row 1, Ada's, already holds it.

So Postgres refuses the INSERT, sends back an error, and stores nothing.

users still holds six rows, and only one has Ada's email.

And what if Ben changes his email to Ada's?

UPDATE users SET email = 'ada@simplified.org'WHERE id = 2;usersidemailnamexp1ada@simplified.orgAda1202ben@simplified.orgBen403cy@simplified.orgCy04dee@simplified.orgDee3005eli@simplified.orgEli406fay@simplified.orgFay75ada@simplified.orgERROR: duplicate key value violates unique constraint "users_email_key"

Say Ben types ada@simplified.org into his settings, by mistake.

So the server sends an UPDATE that sets the email in Ben's row, id 2, to Ada's.

Postgres checks UNIQUE on an UPDATE too, and row 1 already holds that email.

So it refuses the UPDATE, and Ben's row keeps ben@simplified.org.

So what happens when you pick a username on Instagram?

Say Instagram keeps accounts in a table, with UNIQUE on its username column.

InstagramUsernamemaya.devThat username is takenaccountsidusername311sam.codes312maya.dev313leo.kmaya.dev

Your friend already has the username maya.dev.

You try to sign up as maya.dev too.

A row with maya.dev is already there, so a second one would be refused.

So Instagram might tell you "That username is taken", and you pick another.

So UNIQUE refuses any write that would give two rows the same value in its column.

Example: Two sign-ups, one email

Simplified's users table has email text NOT NULL UNIQUE, and holds six rows, with the emails ada@simplified.org to fay@simplified.org. Simplified's server sends Postgres INSERT INTO users (email, name) VALUES ('ben@simplified.org', 'Benji');.

How many rows does users hold afterwards?

Explanation

First, UNIQUE means no two rows may hold the same email, and Ben's row already holds ben@simplified.org.

Then, the new row would be a second one with that email, so Postgres refuses the INSERT and stores nothing.

Therefore, users still holds 6 rows.

Question 1

Simplified's users table has email text NOT NULL UNIQUE, and holds six rows, with the emails ada@, ben@, cy@, dee@, eli@ and fay@simplified.org. Its server sends three INSERTs, one at a time: one with kai@simplified.org, one with cy@simplified.org, and one with lee@simplified.org.

How many rows does users hold afterwards?

rows

Question 2

Simplified's users table has email text NOT NULL UNIQUE. Ben's row, id 2, holds ben@simplified.org, and Dee's row, id 4, holds dee@simplified.org. Its server sends UPDATE users SET email = 'dee@simplified.org' WHERE id = 2;.

What happens?

Question 3

Say Instagram keeps accounts in a table, and you can follow a friend by their username, like maya.dev.

Why might Instagram put UNIQUE on its username column?

Question 4

Say Spotify keeps accounts in a table with email text NOT NULL UNIQUE, and none of these emails is in it yet. One morning, five sign-ups arrive, one at a time, each sending one INSERT: a@gmail.com, b@gmail.com, a@gmail.com again, c@gmail.com, then b@gmail.com again.

How many new rows does the table get?

rows

Question 5

Say an app's users table has email text NOT NULL, with no UNIQUE, and one row holds ada@simplified.org. Then Ada signs up twice more with ada@simplified.org, and each time the app's server sends an INSERT with that email.

Afterwards, how many rows hold ada@simplified.org?

rows

Question 6

Say YouTube keeps channels in a table with UNIQUE on its handle column, and the table holds @mkbhd and @MrBeast.

Which of these writes does Postgres refuse?

The two rules in every key

email text NOT NULL UNIQUE,usersidemailnamexp1ada@simplified.orgAda1202ben@simplified.orgBen403cy@simplified.orgCy04dee@simplified.orgDee3005eli@simplified.orgEli406fay@simplified.orgFay75
UPDATE users SET xp = xp + 10WHERE id = 2;usersidemailnamexp1ada@simplified.orgAda1202ben@simplified.orgBen403cy@simplified.orgCy04dee@simplified.orgDee3005eli@simplified.orgEli406fay@simplified.orgFay752gia@simplified.orgGia0—hal@simplified.orgHal0
id integerPRIMARY KEYGENERATED ALWAYS AS IDENTITY,?NOT NULL+UNIQUEemail textNOT NULL UNIQUE,
attemptsiduser_idlesson_id111212321413541622741usersidemailname1ada@simplified.orgAda2ben@simplified.orgBen3cy@simplified.orgCy4dee@simplified.orgDee5eli@simplified.orgEli6fay@simplified.orgFayNOT NULLUNIQUE?
SELECT id, xp FROM usersWHERE email = 'ada@simplified.org';email text NOT NULL UNIQUE,usersidemailnamexp1ada@simplified.orgAda1202ben@simplified.orgBen403cy@simplified.orgCy04dee@simplified.orgDee3005eli@simplified.orgEli406fay@simplified.orgFay75resultidxp1120logical keyprimary key
id integerPRIMARY KEYGENERATED ALWAYS AS IDENTITY,?NOT NULL+UNIQUEemail textNOT NULL UNIQUE,

Databases · Lesson 1.3

Unique Values

The two rules in every key

Simplified
0:00 / 1:49

So users' email is NOT NULL and UNIQUE: every row holds one, and no two share one.

So when Ada logs in with ada@simplified.org, exactly one row matches.

And Simplified's server finds a row by its id, like an UPDATE that adds 10 XP to id 2.

That only works if id 2 names exactly one row, always.

If two rows held 2, both would get the XP.

And a row with no id could never be pointed at like this.

UPDATE users SET xp = xp + 10WHERE id = 2;usersidemailnamexp1ada@simplified.orgAda1202ben@simplified.orgBen403cy@simplified.orgCy04dee@simplified.orgDee3005eli@simplified.orgEli406fay@simplified.orgFay75

So what does PRIMARY KEY make Postgres check, so id always names one row?

The same two rules email has.

NOT NULL: every row must hold an id.

UNIQUE: no two rows may hold the same id.

So a primary key is NOT NULL and UNIQUE together.

So no row is ever missing an id, and no two rows ever share one.

id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,NOT NULL+UNIQUE

So how does attempt 3 point at Ben, and only Ben?

attemptsiduser_idlesson_id111212321413541622741usersidemailname1ada@simplified.orgAda2ben@simplified.orgBen3cy@simplified.orgCy4dee@simplified.orgDee5eli@simplified.orgEli6fay@simplified.orgFay

Attempt 3 holds user ID 2.

id is UNIQUE, so exactly one row in users holds 2: Ben's.

And id is NOT NULL, so Ben's row has an id for user ID to hold.

So attempt 3 points at Ben, and at no one else.

And what about email, which has the same two rules?

email text NOT NULL UNIQUE,SELECT id, xp FROM usersWHERE email = 'ada@simplified.org';usersidemailnamexp1ada@simplified.orgAda1202ben@simplified.orgBen403cy@simplified.orgCy04dee@simplified.orgDee3005eli@simplified.orgEli406fay@simplified.orgFay75resultidxp1120

email is NOT NULL, so every row holds one.

And it's UNIQUE, so no two rows share one.

So a WHERE on ada@simplified.org finds exactly one row, Ada's, just as an id does.

That's why email is a key too: users' logical key.

It isn't the primary key, because Ada can change her email, and her id never changes.

So a primary key always names exactly one row: no row is missing one, and no two share one.

Example: The two rules in every key

Say Instagram keeps accounts in a table, with username as its primary key. A bug sends an INSERT for a new account that gives an email but leaves the username out, so it would be NULL.

What does Postgres do with the row?

Explanation

First, a primary key is NOT NULL and UNIQUE together, so every row must hold a username.

Then, this row would hold no username, so it breaks NOT NULL.

Therefore, Postgres refuses the row, and nothing is stored.

Question 1

Say Spotify keeps playlists in a table, with playlist_id as its primary key. Your Gym playlist holds playlist_id 501.

At most, how many rows of the table can ever hold playlist_id 501 at the same time?

rows

Question 2

Simplified's users table has id as its primary key, and holds six rows, with the ids 1 to 6. A bug sends Postgres UPDATE users SET id = 2 WHERE id = 5;.

Afterwards, how many rows of users hold the id 2?

rows

Question 3

Simplified's users table has email text NOT NULL UNIQUE. A learner logs in, and Simplified's server sends SELECT id, xp FROM users WHERE email = 'eli@simplified.org';.

Why does it always get back exactly one row, when that email has an account?

Question 4

Simplified's users table has id as its primary key, and email text NOT NULL UNIQUE. Both are NOT NULL and UNIQUE, and both name exactly one row.

Why is id the primary key, and not email?

Question 5

Say YouTube keeps channels in a table, with handle as its primary key, and the table holds two rows: @mkbhd and @MrBeast. Four INSERTs arrive, one at a time: @samcodes, one with no handle (NULL), @mkbhd, and @alexcodes.

How many rows does the table hold afterwards?

rows

Question 6

Say Spotify keeps playlists in a table with id as its primary key, and the table holds the ids 901, 905 and 910.

Which of these INSERTs does Postgres refuse?

Assistant

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