SQLITE 3 foreign key constraint violated without error

Question

From a command line on a stock ubuntu distribution.

$ sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open dumb.db
sqlite> create table a (aid integer primary key, name text);
sqlite> create table b (bid integer, aid integer, name text, foreign key(aid) references a(aid));
sqlite> insert into b values (1,1,'wtf');
sqlite> select * from b;
1|1|wtf
sqlite> select * from a;
sqlite>

It seems to me that the insertion should fail, since there is not an id value of '1' (or any ids, really) in table a.

Am I fundamentally misunderstanding foreign keys, sqlite, or both?

Yes, I previously asked this for sqlite2 which was correctly answered as "insufficient version"

Answer 1

It looks as though you haven't turned Foreign key support on.

add before insert

pragma foreign_keys; /* optional */
pragma foreign_keys = on; /* turns foreign key support on (off by default) */

e.g. :-

drop table if exists b;
drop table if exists a;

pragma foreign_keys;
pragma foreign_keys = on;
create table a (aid integer primary key, name text);
create table b (bid integer, aid integer, name text, foreign key(aid) references a(aid));
insert into b values (1,1,'wtf');
select * from b;
select * from a;
drop table if exists b;
drop table if exists a;

results in :-

insert into b values (1,1,'wtf')
> FOREIGN KEY constraint failed
> Time: 0s