删除级联的SQLite外键无法正常工作

Question

我正在运行以下脚本wtf.sql，在sqlite版本3.16.0上：

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

我需要做什么才能正确删除级联？

Answer 1

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的所有其他地址是否也被删除？

删除级联操作将删除父行时删除任何子行。在这种情况下，这意味着从ZIP删除一行将删除所有相应的ADDRESS行。另一个方向没有类似的机制。