SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[Christian Gollwitzer]

Am 23.11.14 07:32, schrieb Chris Angelico:
> did a sequence
> of commands which ought to have failed, but didn't. Let's see if I can
> recreate this:
>
> rosuav at sikorsky:~$ sqlite3
> SQLite version 3.7.13 2012-06-11 02:05:22
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table foo (val text primary key);
> sqlite> create table bar (val text references foo on delete set null);
> sqlite> insert into foo values ('asdf');
> sqlite> insert into bar values ('asdf');
> sqlite> insert into bar values ('qwer');
> sqlite> select * from foo;
> asdf
> sqlite> select * from bar;
> asdf
> qwer
> sqlite> delete from foo;
> sqlite> select * from foo;
> sqlite> select * from bar;
> asdf
> qwer
>
> So the foreign key is being completely ignored.

SQLite does foreign keys by running a trigger. You need to activate it

	https://www.sqlite.org/foreignkeys.html#fk_enable

Apfelkiste:VecTcl chris$ sqlite3
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA foreign_keys = ON;
sqlite> create table foo (val text primary key);
sqlite> create table bar (val text references foo on delete set null);
sqlite> insert into foo values ('asdf');
sqlite> insert into bar values ('asdf');
sqlite> insert into bar values ('qwer');
Error: foreign key constraint failed
sqlite> select * from foo;
asdf
sqlite> select * from bar;
asdf
sqlite> delete from foo;
sqlite> select * from foo;
sqlite> select * from bar;

sqlite>


Not sure, if this helps the OP, though.

	Christian