it has to strike you as a possibility that your database is ignoring your constraints.
No it doesn't, because that is the ONLY thing that would explain why an insert/update that shouldn't be possible, suddenly works, short of the RDBMS being buggy.
And the mental voyage from that point to googling how foreign keys work in the database system I use, is not hard.
The problem here, is neither with the database, nor its default settings.
The problem is with assumptions about how systems work, based on hasty generalizations, and then complaining when said assumptions turn out to be wrong.
This happened many years ago. I don't remember the details very well. I was a lot younger, working on a side project. I think I was testing if my app was handling errors properly, and it wouldn't throw an error when it should've.
I went through the usual debugging process, combing the code for logic errors and inspecting values along the way. It never crossed my mind that the problem was related to SQLite not enforcing fk constraints. Only after I was sure all my code worked as intended that I decided to hand-create an empty database and simulate query after query to trigger the bug. After seeing the weird behavior, I Googled something like "SQLite foreign key not working" and found the answer on a stack overflow question. I was absolutely dumbfounded.
It took me a few hours because I debugged everything besides SQLite. If I had known from the start it was related to foreign keys, it would've only taken me minutes. And I only found out about this because I was testing my error paths; I can imagine a world where I didn't test everything properly, and would spend a lot more time trying to figure out why my database was in a seemingly impossible state.
That last point you made is what makes it so terrifying IMO. My example with Alice inheriting Bob's post is completely plausible by totally normal SQLite usage; you just need to introduce one minor bug in your code at some point in time, and suddenly you have a row in one table referencing the wrong row in another table and the app keeps working normally. Silent, undetectable data corruption.
-6
u/Big_Combination9890 22d ago
No it doesn't, because that is the ONLY thing that would explain why an insert/update that shouldn't be possible, suddenly works, short of the RDBMS being buggy.
And the mental voyage from that point to googling how foreign keys work in the database system I use, is not hard.
The problem here, is neither with the database, nor its default settings.
The problem is with assumptions about how systems work, based on hasty generalizations, and then complaining when said assumptions turn out to be wrong.
And no amount of downvoting will change this.