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

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")