Databases · Lesson 1.1
Rules Postgres checks on every write
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.
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?
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.
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?
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.
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.
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.
Simplified's schemas hold these four rules. Postgres refuses a write that breaks a constraint.
Which rule is not a constraint?
Databases · Lesson 1.2
Two sign-ups, one email
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.
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.
So what happens to Ada's second sign-up now?
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?
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.
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.
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.
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?
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?
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?
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?
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?
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?
Databases · Lesson 1.3
The two rules in every key
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.
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.
So how does attempt 3 point at Ben, and only Ben?
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 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.
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.
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?
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?
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?
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?
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?
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?