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 6 of 6

Load the three rows yourself

Level 6: Transfer it · Exercise 1 of 1

Same idea as The orphan hunt loses the orphan, new data.

On this copy items has been emptied and foreign keys are on. Fill it from stg_items with one INSERT ... SELECT, in file order: serial trimmed, type_id and daily_rate converted to integers, and the note trimmed with a blank as NULL. Leave out every row with no gear type and every rate that is not whole-number text, and leave the id out.

Data: Trail Gear: build rehearsal. Every run starts from a fresh copy, so nothing you try here is permanent.

I would like help with this exercise

Each run starts from a fresh copy of the data, then shows the rows the question is about.

Table & column names only · ↑ ↓ choose · Tab insert · Esc dismiss

Open in the playground

Query results

Run to see the rows the question is about.

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