找到你要的答案

Q:sqlite3 python3 deleting cascade does not work as expected

Q:sqlite3 Python3删除级联没有象预期的那样工作

I have created these tables:

import sqlite3
conn = sqlite3.connect('scheduler1111111.db')
c = conn.cursor()
c.execute('pragma foreign_keys = ON;')
c.execute('''CREATE TABLE sections
                    (id_sect INTEGER PRIMARY KEY, name TEXT)''')

c.execute('''DROP TABLE IF EXISTS subjects''')
c.execute('''CREATE TABLE subjects
                    (id_subj INTEGER PRIMARY KEY, name TEXT, career TEXT)''')


c.execute('''DROP TABLE IF EXISTS professors''')
c.execute('''CREATE TABLE professors
                    (id_p INTEGER PRIMARY KEY, name TEXT, lastname TEXT, cedula TEXT)''')

c.execute('''DROP TABLE IF EXISTS scheduler''')
c.execute('''CREATE TABLE scheduler
                    (id_sch INTEGER PRIMARY KEY, id_sect INTEGER, id_subj INTEGER, id_p INTEGER, Day TEXT, From_time INTEGER, To_time INTEGER,
                    FOREIGN KEY (id_sect) REFERENCES sections (id_sect)
                     FOREIGN KEY (id_subj) REFERENCES subjects (id_subj),
                     FOREIGN KEY (id_p) REFERENCES professors (id_p) ON DELETE SET DEFAULT)''')

Suppose i have professors Mike X and John Z in the professors tables, and i already have sections in the sections table, and the same on the subjects tables, if i try to add a professor on the scheduler table that is not in the professor table it won't allow me, so it works as i expected it too, however, if i fill a few rows in the scheduler, and input say, the same professor, Mike X in three of the rows, then i go and delete the professor Mike X from the professors table, i thought these three rows on the scheduelr table with Mike X in it would be deleted as well, but what i get is NULL in the column id_p, how can i make it so it actually deletes all of the rows so that delete cascade works as expected and not just NULLS the fields on the child table?

我创建了这些表:

import sqlite3
conn = sqlite3.connect('scheduler1111111.db')
c = conn.cursor()
c.execute('pragma foreign_keys = ON;')
c.execute('''CREATE TABLE sections
                    (id_sect INTEGER PRIMARY KEY, name TEXT)''')

c.execute('''DROP TABLE IF EXISTS subjects''')
c.execute('''CREATE TABLE subjects
                    (id_subj INTEGER PRIMARY KEY, name TEXT, career TEXT)''')


c.execute('''DROP TABLE IF EXISTS professors''')
c.execute('''CREATE TABLE professors
                    (id_p INTEGER PRIMARY KEY, name TEXT, lastname TEXT, cedula TEXT)''')

c.execute('''DROP TABLE IF EXISTS scheduler''')
c.execute('''CREATE TABLE scheduler
                    (id_sch INTEGER PRIMARY KEY, id_sect INTEGER, id_subj INTEGER, id_p INTEGER, Day TEXT, From_time INTEGER, To_time INTEGER,
                    FOREIGN KEY (id_sect) REFERENCES sections (id_sect)
                     FOREIGN KEY (id_subj) REFERENCES subjects (id_subj),
                     FOREIGN KEY (id_p) REFERENCES professors (id_p) ON DELETE SET DEFAULT)''')

假如我在教授表教授Mike X和约翰Z,和我已经有部分在节表,和相同的科目表,如果我尝试添加一个教授对调度表不在教授表它不会允许我这样的作品与我预料的一样,然而,如果我填写调度几排,和输入说,同样的教授,迈克X三行,我便删除迈克教授X教授的桌子上,我以为这三行的scheduelr表迈克X会被删除为好,但什么我得到的是空的列中的id_p,怎样才能使它完全删除所有行,级联删除的工作预期,不只是对空值的字段e子表?

answer1: 回答1:

but what i get is NULL in the column id_p

Well, yes, because you told SQLite to do that:

FOREIGN KEY (id_p) REFERENCES professors (id_p) ON DELETE SET DEFAULT

The ON DELETE SET DEFAULT part means "when the row referenced by id_p is deleted, set the id_p column to its default value", which for a NOT NULL column is NULL unless otherwise specified.

Change ON DELETE SET DEFAULT to ON DELETE CASCADE.

但我得到的是空的列中的id_p

嗯,是的,因为你对SQLite做:

FOREIGN KEY (id_p) REFERENCES professors (id_p) ON DELETE SET DEFAULT

对删除默认设置部分的意思是“当被id_p删除该行,将id_p列为默认值”,这对于一个非空的列为空,除非另有规定。

更改删除设置默认到删除级联。

python  python-3.x  sqlite3