#254 Data modelling 6.8 min
Constraints That Catch Bad Data: PK, FK, CHECK
Without constraints SQLite loaded all 8 rows, 5 of them bad; with constraints only 3 got in.
Predict the three runs
Copy the code folder first. Read batch.py and schema.py, then write down how many rows you expect to load in the loose and checked runs and the total quantity for each. Run python run.py loose and python run.py checked and compare. You should see 8 of 8 with a total of 11, then 3 of 8 with a total of 7.
A hint
Count which of the eight rows break which rule.
Allow one more status
In schema.py, add 'lost' to the list of allowed statuses in the CHECKED table. Predict which order is now accepted, then run python run.py checked. You should see 4 of 8 rows loaded and a total quantity of 8. Take 'lost' out of the list again afterwards, because the next task expects the original rules.
A hint
Order 105 had the status lost and a quantity of 1.
Remove NOT NULL from the quantity
In schema.py, delete the words NOT NULL from the qty column of the CHECKED orders table, keeping CHECK (qty > 0). Predict what happens to order 106, which has no quantity, then run python run.py checked. You should see 4 of 8 rows loaded and a total quantity of 7, because the missing quantity adds nothing to the total.
A hint
A check whose result is null does not fail.
Switch foreign keys on in the traps
In run.py, in the traps function, add the line db.execute("PRAGMA foreign_keys = ON") straight after the db = sqlite3.connect line. Run python run.py traps. You should see the orphan now rejected with FOREIGN KEY constraint failed, while the NULL quantity and the text in an INTEGER column are still accepted. The first printed label still says foreign keys off because it is only text.
A hint
Only one of the four traps depends on foreign keys.
These tasks are for you to try on your own machine. Nothing is checked or saved.
What this does not show
Eight made-up orders in SQLite only; no load cost is measured and rejected rows are printed, not stored.
