SQLite Forum

about "strict" mode
I'm not against your suggestion, but both the web page and you missed out a vital consideration, so I'm going to bring it up here.

There's a nasty question of whether the strict setting should be stored in each database file.  Or has to be set as each connection is opened like the connection timeout.  But that's beyond the scope of my post.

Consider an existing database which was created without strict mode.  It has all the sorts of faults listed in <https://sqlite.org/src/wiki?name=StrictMode>: missing keys, table definitions and TRIGGER with the wrong quotes, NULL primary keys values, all that badness, and more that people haven't thought of yet.  A new programmer takes over the project and uses their own code (not a custom utility like the CLI) to turn on strict mode.  What happens ?

Should turning on strict mode involve a lengthy check for all these things ?

1) Running the checks when switching to strict mode: Could take minutes or, rarely, hours.
2) Not running the checks when switching to strict mode: All operations throughout all programs which access the database now have to deal with formerly unanticipated error reports from a lot of API calls.
3) A strict setting is stored in each database file, and is set like <code>PRAGMA page_size</code>.  To convert an existing database to strict mode you must <code>VACUUM</code> it.  The extra checks take place during the <code>VACUUM</code>.  <code>VACUUM</code> is changed so that it can return syntax error and other error results.  <code>VACUUM</code> is changed so that it will check the integrity of foreign keys if strict mode has just been turned on.

Given that the check for strict-compliance could already be lengthy, and relies on the integrity of the database, should it start off with an integrity_check ?