CoursesBackend engineering from first request to production
Puzzle 6 · 7 of 18
Can a bookmark point to a missing owner?
Model relationships and query accounts that have no saved rows.
All puzzles in this course
The problem
Checking that an owner exists in a route only protects that route. What happens when another writer inserts a row?
See it
Scroll sideways to see the full diagram.
Ada and Ben both have accounts.
Step 1 of 4Only Ada has a saved row.
Step 2 of 4A left join keeps both owners.
Step 3 of 4Count the matching bookmark names.
Step 4 of 4
The building block
A table stores rows with named columns. A primary key identifies one row. A foreign key asks the database to check that a referenced owner exists. The laboratory enables that check on its SQLite connection. A join combines rows by a matching key. A left join keeps accounts with no match. Counting the matched bookmark name yields zero for Ben. Counting every output row instead would count the empty match as one.
Write the intermediate rows before counting
Start with accounts Ada and Ben and a single bookmark, river, owned by Ada. A left join from accounts produces two pairs: (Ada, river) and (Ben, missing). Counting the nonmissing bookmark names in each group gives one and zero. An inner join drops Ben's pair completely. Adding a second Ada bookmark changes only her count. The foreign key guards a different promise: inserting a bookmark for an account that does not exist should fail when the constraint is enabled.
Try it
Optional local lab
Run the owner constraint and count fixtures. Change the left join to an inner join and predict which owner disappears.
Check yourself
Sources
What you now know
A foreign key enforces a relationship; a left join can include owners with zero bookmarks.
Answer every question above correctly to unlock the next puzzle.
Something wrong or unclear on this page? Tell me: a puzzle that didn't click is a bug in the puzzle.