learning-it by example

#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.

1. When a made-up batch of eight orders, five of them bad on purpose, was loaded into tables with no constraints, how many rows went in and what was the total quantity?
2. In SQLite, why did a row with a NULL quantity get through a table that had only a CHECK (qty > 0) on that column?
3. In SQLite, which kind of table rejected the text 'two' inserted into an INTEGER column?
4. In SQLite, why did an insert that pointed at a missing customer (customer 9) get accepted by a table that has a foreign key?
5. In the SQLite orders table with constraints, which rule makes an order with the status 'lost' fail?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. 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.

  2. 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.

  3. 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.

  4. 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.

Up next in Data modelling

More in Data modelling

All Data modelling videos

Related videos