How to make ON DELETE CASCADE work in sqlite 3.7.4?

Question

I checked the list of functions several times, and it seems that cascading should work. When I execute this python script:

#!/usr/bin/env python3 import sqlite3 print(sqlite3.sqlite_version) con = sqlite3.connect(':memory:') a = "create table a (id integer primary key, name text)" con.execute(a) b = "create table b (id integer primary key, r integer, foreign key(r) references a(id) on delete cascade)" con.execute(b) con.commit() a = "insert into a (name) values (\"abc\")" con.execute(a) con.commit() print(con.execute("select * from a").fetchall()) a = "insert into b (r) values (1)" con.execute(a) con.commit() print(con.execute("select * from b").fetchall()) a = "delete from a where id=1" con.execute(a) con.commit() print(con.execute("select * from b").fetchall()) print(con.execute("select * from a").fetchall()) 

I get the following results:

 3.7.4 [(1, 'abc')] [(1, 1)] [(1, 1)] [] 

Which proves that cascading did not happen. What have I done wrong, or what are the solutions to get the same result as cascading?

Answer 1

SQLite foreign keys are disabled for compatibility. You need to enable them manually immediately after each connection to the database.

con.execute("PRAGMA foreign_keys = ON")

Answer 2

In this question, it is better to answer Thibault J : Enable sqlite integrity check in django which states:

 from django.db.backends.signals import connection_created def activate_foreign_keys(sender, connection, **kwargs): """Enable integrity constraint with sqlite.""" if connection.vendor == 'sqlite': cursor = connection.cursor() cursor.execute('PRAGMA foreign_keys = ON;') connection_created.connect(activate_foreign_keys)