The invalid rate hides behind zero
CAST, GLOB, why a CHECK cannot see the source
Trail Gear's daily rates, which arrived as text: 8, 12, 9, -3, the word unknown, and 10.
Can you
- Say what CAST does to text that is not a number
- Find the values that break a text rule with GLOB before converting them
- Explain why a CHECK on the target table cannot catch a value CAST invented
The reading
Read the whole chapterCAST converts; it does not certify
TG-105's rate arrived as the word unknown. CAST(daily_rate AS INTEGER) turns it into 0, with no error and no warning. CAST reads digits from the start of the text and stops at the first character that does not fit; when there are no digits at all, the answer is zero.
SELECT serial, daily_rate, CAST(daily_rate AS INTEGER) AS converted_rate
FROM stg_items
ORDER BY rowid;items has a CHECK on daily_rate: an integer, zero or more. That CHECK sees the converted 0 and accepts it, because a CHECK can only see the value it is handed, never the text in the file. An item that rents for nothing a day enters the database, and every report built on it treats the gear as free. TG-104's -3 is refused, but only because this rule happens to forbid negatives.
So test the text before you convert it. The rehearsal's rule: after TRIM, the rate is not empty and every character is a digit from 0 to 9. TRIM(daily_rate) = '' OR TRIM(daily_rate) GLOB '*[^0-9]*' finds the rows that break it. GLOB matches a pattern: * stands for any run of characters, and [^0-9] for one character that is not a digit. TG-104 breaks the rule with its minus sign, TG-105 with every letter.
SELECT serial, daily_rate
FROM stg_items
WHERE TRIM(daily_rate) = ''
OR TRIM(daily_rate) GLOB '*[^0-9]*'
ORDER BY rowid;The rule is this rehearsal's, not a universal one. It refuses decimals, which suits whole-dollar daily rates and would be wrong for a wage with cents. Your client's dictionary says what a valid value looks like, and your rule follows it. Whatever the rule, keep the rejected row and its reason: load_audit gives both TG-104 and TG-105 the reason 'Rate is not an unsigned whole number'.
Rule of thumb. Test the text, then convert it. A CHECK sees only what CAST handed it.
Going deeper (optional, not on the exam)
GLOB is case-sensitive and uses * and ? where LIKE uses % and _. LIKE has no brackets, so it cannot say one character that is not a digit.
CAST('21,50' AS REAL) is 21.0. The conversion stops at the comma, and a wage written with a decimal comma loses its cents without an error.
Do not add OR IGNORE to make a load script finish. INSERT OR IGNORE skips every row that breaks a UNIQUE, NOT NULL or CHECK constraint without saying which row or which rule, so the count comes out short and the evidence is gone. It does nothing for a foreign key, which still refuses the statement.
By default a failed constraint aborts the one statement that hit it, so an INSERT ... SELECT that meets one bad row loads none of its rows. Statements that ran earlier in the script stay done. That is SQLite's ABORT behaviour, and it is why the load filters the bad rows out with a WHERE instead of waiting for them to fail.
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.