Filling the Wishlist

The table is ready, so let's fill it. Before launching the wishlist, the shop asked a few customers to try it out, and they saved their first wishes. Some of them wished for games they've bought since, others for games they haven't bought. A few wished for Robot Rebellion and Mystery Manor, which aren't out yet, and two of them, Henry Scott and Max Baker, have never ordered anything at all.

In the editor, you'll find the project file with the solution of the last exercise. From now on, the queries of earlier exercises are commented out, so that Results show only the new ones. The CREATE TABLE (and soon the INSERT) keeps running, because each run starts with a fresh database.

A reminder: one INSERT can add many rows. After the table's name, list the columns in parentheses, and after VALUES, write one pair of parentheses for each row, separated by commas. Text and dates go in single quotes:

INSERT INTO reviews (game_id, customer_id, stars, comment)
VALUES
    (9, 4, 4, 'Great puzzles.'),
    (4, 7, 5, NULL);

Now the rules you gave the table are at work. A wish of a customer who doesn't exist, such as (99, 1, '2025-06-01'), stops the INSERT with the error FOREIGN KEY constraint failed. The same game twice on one wishlist stops it with UNIQUE constraint failed: wishlist.customer_id, wishlist.game_id: a primary key doesn't allow two rows with the same values, just like UNIQUE, so the message names both its columns. And when one row of an INSERT breaks a rule, the whole statement fails and adds none of its rows.

Typical mistake: a date without quotes. For SQLite, 2025-10-01 isn't a date but a calculation, 2025 minus 10 minus 1, so the wish would get the date 2014. And there's no error: a STRICT table turns the number into the text '2014' without complaint, because nothing gets lost on the way. Dates always go in quotes.

For people, a table of IDs is hard to read. To see who wants which game, join the wishlist with customers (on customer_id) and with games (on game_id), as you learned in the lesson about joins. With short table aliases, such as w, c and g, the query stays easy to read.