SQLite Forum

IS NULL optimization on NOT NULL constraints breaks on corrupted databases
>  During my perplexity on the point, I've been tempted to parody it, along lines of" "I crossed out 'Apples' on this bag of fruit and wrote 'Oranges', then pulled an apple out." But it was too crude and not quite on point. And likely to seem unkind.

Actually, that is a very good analogue, in more ways than one!

The 'bag of fruit' represents a whole database.  
The 'label' represents the database schema.  
The 'apple' represents the database contents.

So if you receive a 'bag of fruit' that is labelled 'oranges', and pull out an 'apple' you can conclude that the 'bag of fruit' is corrupt.  Clearly when receiving a 'bag of fruit' from a 'source of ill-repute' if might be advantageous to check that the contents of the bag match the label.

Luckily in SQLite3 there is command(s) to automate this inspection to ensure that the contents of the bag match the label -- `pragma integrity_check` and `pragma foreign_key_check` -- if both of these pass then the label (schema) and contents (data) are consistent and not corrupt.