如何使 ON DELETE CASCADE 在 sqlite 3.7.4 中工作? [英] How to make ON DELETE CASCADE work in sqlite 3.7.4?

问题描述

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

I checked the feature list 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())

我得到了这些结果:

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

这证明没有发生级联.我做错了什么或者获得与级联相同结果的解决方案是什么?

Which proves that cascading didn't happened. What I did wrong or what are the solutions to get same result as cascading?

推荐答案

出于兼容性目的，SQLite 外键被禁用.您需要在每次连接到数据库后立即手动启用它们.

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

con.execute("PRAGMA foreign_keys = ON")

这篇关于如何使 ON DELETE CASCADE 在 sqlite 3.7.4 中工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！