SQLITE 3 foreign key constraint violated without error-CodePudding

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"

CodePudding user response：

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