Add a row that fits
INSERT with named columns, defaults, a parent that exists
A new item on the menu and a new hire at the Lincoln Park shop, each a row that has to satisfy the table's rules.
Can you
- Insert a row by naming its columns and leaving the id out
- Say what an unnamed column receives
- Read a constraint failure as a diagnosis
The reading
Read the whole chapterAdd a row that fits
INSERT INTO menu_items (name, category, size, price) VALUES ('Chai Latte', 'Tea', 'Large', 5.50) adds one row. Three habits live in that one statement. Name the columns: SQL lets you skip the column list and supply every value in table order, and every script that does so breaks the day someone adds a column. Leave id out: INTEGER PRIMARY KEY hands out the next number, and the row gets 46 because 45 was the last. Read changes() afterwards, or look at the row: one row, the one you meant.
INSERT INTO menu_items (name, category, size, price)
VALUES ('Chai Latte', 'Tea', 'Large', 5.50);A column you do not name gets its DEFAULT if the table has one, and NULL if it does not. calories, is_seasonal and description are not in the list above, so the new row has NULL in all three. That is fine when NULL means 'not recorded yet' and wrong when a report will treat it as zero.
Every constraint the table was created with is a rule about which inserts fail. A menu item with no name is refused: NOT NULL constraint failed: menu_items.name. An employee whose shop_id names a shop that does not exist is refused, once foreign keys are on: FOREIGN KEY constraint failed. The message names the rule that fired, which is why you paste error messages into reports as text: the wording is the diagnosis. A refused insert is not a failure of the exercise. It is the table doing its job, and you will write inserts that must fail on purpose in Lab 10.
Rule of thumb. Name the columns, leave the id out, and expect the table to refuse a row that breaks its rules.
Going deeper (optional, not on the exam)
The positional form, INSERT INTO menu_items VALUES (NULL, 'Chai Latte', 'Tea', 'Large', 5.50, NULL, NULL, NULL), runs. It also puts a value in the wrong column the day the table's column order changes, and nothing tells you.
The ladder
Start at the first rung even if the idea looks familiar: it is answerable from the reading alone, and the climb is where the difficulty lives. A wrong answer sends you back to the paragraph that teaches it, then to a hint, then to the worked answer.
Learn
- Practise this rung
Level 1: Watch it answered
- Practise this rung
Level 2: Fill the blank
Practise
- Practise this rung
Level 3: Predict the result
- Practise this rung
Level 4: Write it yourself
Master
- Practise this rung
Level 5: Repair it
- Practise this rung
Level 6: Transfer it
Keep practising
The whole bank for this concept, in any order, with the same review, hint and worked answer.
Practice here is evidence for you and your instructor, never a grade. Grades live in Canvas.