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

Can a bookmark point to a missing owner? Accounts Bookmarks Left join Ada 1, Ben 0

Scroll sideways to see the full diagram.

  1. Ada and Ben both have accounts.

    Step 1 of 4
  2. Only Ada has a saved row.

    Step 2 of 4
  3. A left join keeps both owners.

    Step 3 of 4
  4. Count 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

Run the browser experiment

Optional local lab

Run the owner constraint and count fixtures. Change the left join to an inner join and predict which owner disappears.

Open the local exercise files

Check yourself

Ben has no bookmarks. What should his count be?

Not quite. The account exists but has no matching bookmark. Re-read that part ↑

Right. A foreign key enforces a relationship; a left join can include owners with zero bookmarks.

Which change makes Ben vanish from the result?

Not quite. An inner join emits matching pairs only. Re-read that part ↑

Right. An inner join emits matching pairs only.

What stops a stored bookmark from referring to an absent account?

Right. Reading a relationship cannot enforce writes to it.

Not quite. Reading a relationship cannot enforce writes to it. Re-read that part ↑

Sources

What you now know

A foreign key enforces a relationship; a left join can include owners with zero bookmarks.