SQLite删除键上的外键不起作用 [英] SQLite FOREIGN KEY ON DELETE CASCADE not working

问题描述

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

I'm running the following script, wtf.sql, on SQLite Version 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中键入以下内容：

Typing the following into bash:

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

返回以下元组：

90210|Beverly Hills|CA

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

What do I need to do to have deletes cascade properly?

推荐答案

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 的其他地址？

What should happen now? Should all other addresses referring to 90210 also be deleted?

An ON DELETE CASCADE操作会在删除父行时删除所有子行。在这种情况下，这意味着从 ZIP 中删除​​一行将删除所有相应的 ADDRESS 行。相反，没有类似的机制。

An ON DELETE CASCADE action deletes any child rows when a parent row is deleted. In this case, this means that deleting a row from ZIP will delete all corresponding ADDRESS rows. There is no similar mechanism in the other direction.

这篇关于SQLite删除键上的外键不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！