Practise · The orphan hunt loses the orphan

Opening your saved work…

The reading

Keep the row that has no parent

TG-103 says its gear type is 99. gear_types has two rows, 1 for Trail gear and 2 for Water gear. A row whose foreign key points at nothing is an orphan, and an orphan in a file is easy to lose before anyone sees it.

Look for it with an INNER JOIN and it disappears. The join keeps only the staged rows that found a parent, so WHERE g.id IS NULL is asked of five rows that each have one. The query returns nothing, and nothing looks exactly like no orphans.

A LEFT JOIN from staging to the parent keeps all six staged rows. TG-103 comes back with NULL in every gear_types column, and the test is on the parent's key: WHERE g.id IS NULL. The ON clause converts the staged text, g.id = CAST(s.type_id AS INTEGER), and that CAST is safe only because every type_id in this file was checked first and is all digits.

SELECT s.serial, s.type_id, g.name AS gear_type
FROM stg_items s
LEFT JOIN gear_types g ON g.id = CAST(s.type_id AS INTEGER)
ORDER BY s.rowid;

Six rows. TG-103 keeps its row, with NULL where a gear type's name would be. Open in the playground

Rule of thumb. Hunt orphans from the child's side with a LEFT JOIN, and test the parent's key for NULL.

Going deeper (optional, not on the exam)

With foreign keys on, an INSERT ... SELECT that includes TG-103 is refused as a whole: FOREIGN KEY constraint failed, and none of its rows load. With them off, the orphan loads without a word. PRAGMA foreign_key_check lists every row whose foreign key points at nothing, which is how you find what an earlier load let in.

PRAGMA foreign_keys = ON belongs to the connection, not the file. Every new sqlite3 session starts with enforcement off, which is why each of the kit's scripts turns it on in its first line.

What happens to the orphan is the client's call

Finding TG-103 is SQL. Deciding what happens to it is not. There are three honest answers: exclude the row and write down why; correct it, when the client confirms what the type should have been and sends a corrected file; or let it in with no type, if the business allows an item without one and the column is declared to allow NULL. Each needs a reason the client would sign.

The dishonest fourth answer is to invent a gear type 99, call it Unknown, and watch the error go away. The foreign key is satisfied, and every report now has a category nobody defined. The rehearsal excludes TG-103 with the reason 'Unknown gear type', and the reconciliation lists it by name.

Rule of thumb. An orphan is a question for the client, not a parent to invent.

Going deeper (optional, not on the exam)

A foreign key can say what happens when its parent row is deleted: ON DELETE CASCADE removes the children too, and ON DELETE SET NULL empties their key. The kit declares neither, so deleting a gear type that items point at is refused while foreign keys are on. Read the CREATE TABLE before you assume what a delete will do.

The orphan hunt loses the orphan · LEFT JOIN from staging to the parent, exclusion as a decision · level 1 of 6

Every staged row beside its parent

Level 1: Watch it answered · Exercise 1 of 1

Show every staged row beside the gear type it names: serial, type_id and the gear type's name for all six rows in file order, keeping the rows that find no parent.

Data: Trail Gear: build rehearsal.

Why it works, clause by clause

FROM stg_items s: start from the child side, the rows that need a parent.
LEFT JOIN gear_types g ON g.id = CAST(s.type_id AS INTEGER): the staged ids are text, so the ON converts them, and every staged row stays, matched or not.
TG-103 comes back with NULL in gear_type. That NULL is the orphan, and it is the evidence behind load_audit's Unknown gear type.

From the reading: Keep the row that has no parent, paragraph 3

I would like help with this exercise

SELECT s.serial, s.type_id, g.name AS gear_type
FROM stg_items s
LEFT JOIN gear_types g ON g.id = CAST(s.type_id AS INTEGER)
ORDER BY s.rowid;

Open in the playground

Query results

Run it to see the result here.

Practice here is evidence for you and your instructor, never a grade. Grades live in Canvas.