如何使ON DELETE CASCADE在sqlite 3.7.4中工作？

Question

我查了几次功能列表，似乎级联应该可以工作。 当我执行这个python脚本：

#!/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()) 

我得到这些结果：

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

这证明级联没有发生过。我做错了什么，或者有什么解决方案可以获得与级联相同的结果？

Answer 1

为了兼容性目的，禁用SQLite外键。您需要在每次连接到数据库后手动启用它们。

con.execute("PRAGMA foreign_keys = ON")

Answer 2

有用户Thibault J一个更好的答案了这样一个问题：Enable integrity checking with sqlite in django它说：

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)