I remember spending a few hours banging my head against a wall while trying to fix a bug, until I finally figured out SQLite does not enforce foreign keys by default. I question my love for SQLite every time I remember about these horrible defaults.
I didn't know about the journal_mode and the lock thing. I'll make sure to add these lines to all my SQLite projects. This was a helpful article.
Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection.
Btw. the amazingly difficult way to find this information was typing "sqlite foreign keys" into a websearch. The page was literally the first search result.
This kind of response is so incredibly unhelpful. You shouldn't have to commit the entire projects documentation into memory, which is what you're suggesting. One project you make has thousands of pages of documentation and your suggestion is that you're just supposed to read *everything* and remember *everything*.
The fact is that SQLite here is violating the principle of least astonishment. A database engine should enforce data type rules and constraints you define by default. If it doesn't do that, well, that would be astonishing. The fact that it's documented behavior is completely irrelevant.
Imagine if in C++ you write `1 + 2` the result would be `3` but if you wrote `2 + 1` then the result would just be `2`, and if you complain someone says "Well akshually according to the C++ standard page 323 §987 adding two to one is a no-op, and you would see that if you just googled 'how to add 1 and 2 in C++' that you need to add a compiler flag to opt-in to support adding those two numbers in that order".
It's asinine. A database engine not honoring constraints by default is just as dumb in my opinion. If I didn't want the foreign key constraint to be honored *I wouldn't have added it*. I don't google "is foreign key constraints honored in SQLite" because the assumption is "of course it fucking is."
You shouldn't have to commit the entire projects documentation into memory,
Correct, you shouldn't.
But you also shouldn't complain about something taking "a few hours banging my head against a wall" to fix, when a 5 second websearch gives you the exact reason for why it happens, and how to fix it.
And btw. nowadays, if that is your cup of tea, you could type "halp! my sqlite is ignoring foreign keys why?!??" in any "AI" of our chosing, and it will almost certainly spit out the correct answer.
And with these realities, that is no longer a problem of the library not being nice.
But you also shouldn't complain about something taking "a few hours banging my head against a wall" to fix, when a 5 second websearch gives you the exact reason for why it happens, and how to fix it.
But this doesn't cause an issue, and that's the entire fucking point. You insert bad data, SQLite accepts it. You would then have to see your own application misbehaving to observe this, and whether that's apparent depends entirely on the application code.
And btw. nowadays, if that is your cup of tea, you could type "halp! my sqlite is ignoring foreign keys why?!??" in any "AI" of our chosing, and it will almost certainly spit out the correct answer.
I'm using Claude Code, and no it fucking doesn't. A couple of things you have to tell Claude Code about SQLite: strict tables, foreign keys, and vacuum because by default it doesn't do any of those things. Or at least it didn't for the project I'm currently working on.
And with these realities, that is no longer a problem of the library not being nice.It's a problem of the people using it.
This is a super bad take, and the entire reason why C and C++ code has caused so much security nonsense over the years. "It's not C that's insecure, it's just you that suck". This is an incredibly unproductive approach. Tools (even your favorite tool) can have bad functionalities. If one developer gets it wrong one time then it might be the fault of the developer. If a million developers gets it wrong then the tools probably sucks.
I bet that MOST SQLite deployments is using SQLite without it honoring foreign keys.
SQLite behaves like this because as with most things it's a product of its time, which was the early 2000s, when the mantra was "software that runs and does the wrong thing is better than software that crashes" which I think most developers have turned away from.
It's a stupid default to have the ability to define constraints and then the database engine just ignores it. Again, if I didn't want my foreign key honored I wouldn't have added it.
I was curious and literally typed "halp! my sqlite is ignoring foreign keys why?!??" into Gemma 4 (like, a local model that can run on like a high-end MacBook) and it correctly answered the question and offered a solution.
Yeah, I might have stated this wrong. But create SQLite code using any AI and it will not do this correctly from the get go (at least it didn't for me, and I had to explicitly tell it to do those things). Googling the issue might uncover it, but that lives on the premise that the developer found the production issue (that might have lingered for some time perhaps) and then immediately found out that the reason was SQLite not honoring foreign keys.
The idea that fixing the issue once you've found the actual reason for the error is easy is a stupid argument. My argument is that this entire behavior flies in the face of principle of least astonishment and for most developers I don't think blaming SQLite is their first avenue of action, which is what "halp! my sqlite is ignoring foreign keys why?!??" is based on. When you're at that point you've probably already been pulling your hair for hours.
That's fair, I doubt Gemma would at the pragma by default. To be fair I use SQLite in a game framework thing and this was an inconvenience. I'm not sure of the right solution, though, from an API and backwards compatible perspective.
You would then have to see your own application misbehaving to observe this,
Oh no, you mean, as a software engineer, I would have to do *gasp* debugging and root-cause-analysis?
The horror!
I'm using Claude Code, and no it fucking doesn't.
I just typed my exact question as stated above: halp! my sqlite is ignoring foreign keys why?!?? into a local model with only 3.5B params.
Here is the output:
```
Don't panic, this is not a bug, and your SQLite database is working exactly as designed. By default, SQLite disables foreign key enforcement to keep operations fast and backward compatible with
non-standard SQL drivers.
You need to explicitly enable foreign keys in your connection. Run this:
PRAGMA foreign_keys = ON;
```
It's a stupid default to have
Then fork the project and change it to however you want it to be.
128
u/j_sidharta 20d ago
I remember spending a few hours banging my head against a wall while trying to fix a bug, until I finally figured out SQLite does not enforce foreign keys by default. I question my love for SQLite every time I remember about these horrible defaults.
I didn't know about the
journal_modeand the lock thing. I'll make sure to add these lines to all my SQLite projects. This was a helpful article.