SQLite的外键DELETE CASCADE不工作

问题描述：

我运行下面的脚本，wtf.sql，在SQLite的版本3.16.0：SQLite的外键DELETE CASCADE不工作

PRAGMA foreign_keys = ON; 

CREATE TABLE 'ZIP'(
    'Zip' INTEGER PRIMARY KEY NOT NULL, 
    'City' TEXT NOT NULL, 
    'State' TEXT NOT NULL 
); 
CREATE TABLE 'ADDRESS'(
    'Address_id' INTEGER PRIMARY KEY AUTOINCREMENT, 
    'Line_1' TEXT NOT NULL, 
    'Line_2' TEXT, 
    'Zip' INTEGER NOT NULL, 
    FOREIGN KEY('Zip') REFERENCES ZIP('Zip') ON DELETE CASCADE 
); 

INSERT OR IGNORE INTO ZIP('Zip','City','State') 
    VALUES ('90210','Beverly Hills','CA') 
; 
INSERT INTO ADDRESS('Line_1','Line_2','Zip') 
    VALUES ('1234 Overpriced House Street','c/o Bob','90210') 
; 
DELETE FROM ADDRESS 
    WHERE Zip = '90210' 
; 
SELECT * 
    FROM ZIP 
;

键入以下内容的bash：

$ sqlite3 bits-n-books.db < wtf.sql

返回以下元组：

90210|Beverly Hills|CA

我需要怎么做才能正常删除级联？

答

INSERT INTO ADDRESS(Line_1,Line_2,Zip) 
    VALUES ('1234 Overpriced House Street','c/o Bob','90210'), 
     ('667 Another Street','2nd row','90210'); 

DELETE FROM ADDRESS WHERE Line_2 = '2nd row';

现在该怎么办？所有其他地址是否也被删除了？90210？

当父行被删除时，ON DELETE CASCADE action删除所有子行。在这种情况下，这意味着从ZIP删除一行将删除所有对应的ADDRESS行。在另一方面没有类似的机制。

SQLite的外键DELETE CASCADE不工作

相关推荐