TIL: SQLite does not check foreign keys by default

I have to admit: I generally missed all the hype around putting SQLite everywhere, especially in production, that has been going on for a while. To me SQLite is generally a dev (or local) database, one that is simple to setup. But if I need something “searious”, I always reach for battle-tested PostgreSQL.

For that reason, I never paid too much attention to configuring SQLite, mostly using it just as is. So I was quite disappointed seeing this:

sqlite> create table users (id integer primary key, name text);
sqlite> create table posts (id integer primary key, title text, user_id integer, foreign key(user_id) references users(id));
sqlite> insert into users (id, name) values (1, 'john');
sqlite> insert into posts (id, title, user_id) values (1, 'my first post', 2);
sqlite> select * from posts;
╭────┬───────────────┬─────────╮
│ id │     title     │ user_id │
╞════╪═══════════════╪═════════╡
│  1 │ my first post │       2 │
╰────┴───────────────┴─────────╯

Ugh, what? That is not right. There is a foreign key, but I just happily inserted a row violating it!

It turns out you need to opt in for foreign key checks:

sqlite> pragma foreign_keys = on;
sqlite> insert into posts (id, title, user_id) values (2, 'my second post', 2);
Error near line 9: FOREIGN KEY constraint failed

Ok, now we’re talking!

Sounds like pretty important thing to remember when you are working with your SQLite.