Sqlite browser foreign key6/15/2023 ![]() (note: some of these defaults are configurable when compiling sqlite from scratch, but if you're dynamically linking with an OS-provided instance of the library, you can't really do that). This tends to be explicitly problematic if you have to perform one or more ad-hoc queries using the SQLite command line on an app's database, and forget to apply the relevant PRAGMAs! I wish there was a way to add "default" PRAGMAs on a sqlite database file to avoid this. But, here again, that's not the default: you need `PRAGMA auto_vacuum = INCREMENTAL`. If you're unsure, it's a good idea to enable incremental auto-vacuum to keep that option open. Full auto-vacuum cannot be enabled after you start writing to the database unless you enabled incremental auto-vacuum. The main one being that reads will conflict with writes if you don't enable it! (which is a problem in a multi-threaded environment) Unfortunately, that's another feature you must remember to enable: `PRAGMA journal_mode = WAL ` (for obvious reasons, this one "stays enabled" after you turn it on). There are practically no downsides (and a number of upsides) to using the WAL journalling mode (at least for "use a local database and store it on the disk" use cases). ![]() You need to enable them using `PRAGMA foreign_keys = ON `. ![]() Foreign key checking (and cascading deletion for that matter) are turned off by default. I love SQLite (and use it in a large number of places in our application stack!), but I feel that it suffers from a case of bad defaults.
0 Comments
Leave a Reply. |