SQLite的ON DELETE CASCADE约束未正常工作。

huangapple
go评论101阅读模式
英文:

SQLite ON DELETE CASCADE constraint not working properly

问题

我试图删除父表中的一个条目，但与我预期的不同，子表中的相应条目没有被删除。

这是我创建的两个表：

  1. CREATE TABLE
  2.     IF NOT EXISTS "pages" (
  3.         "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  4.         "title" TEXT,
  5.         "author" TEXT REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
  6.         "creation_date" DATE,
  7.         "publication_date" DATE
  8.     );
  10. CREATE TABLE
  11.     IF NOT EXISTS "blocks"(
  12.         "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  13.         "page_id" INTEGER REFERENCES pages(id) ON DELETE CASCADE ON UPDATE CASCADE,
  14.         "type" TEXT,
  15.         "content" TEXT,
  16.         "position" INTEGER
  17.     );

这是我运行的查询：

  1. DELETE FROM pages WHERE id = ?

pages表的条目被正确删除，但删除不会传播。我还添加了PRAGMA foreign_keys = ON;指令，但没有解决问题。任何帮助都将不胜感激。

英文:

I'm trying to delete an entry in a parent table but the corresponding entries in the child table are not getting deleted as I expected.
These are the two tables that I created:

  1. CREATE TABLE
  2.     IF NOT EXISTS "pages" (
  3.         "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  4.         "title" TEXT,
  5.         "author" TEXT REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
  6.         "creation_date" DATE,
  7.         "publication_date" DATE
  8.     );
  10. CREATE TABLE
  11.     IF NOT EXISTS "blocks"(
  12.         "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  13.         "page_id" INTEGER REFERENCES pages(id) ON DELETE CASCADE ON UPDATE CASCADE,
  14.         "type" TEXT,
  15.         "content" TEXT,
  16.         "position" INTEGER
  17.     );

And this is the query I'm running:

  1. DELETE FROM pages WHERE id = ?

The entry of the pages table gets deleted correctly, but the deletion doesn't propagate. I also added the PRAGMA foreign_keys = ON; instruction, but that didn't solve the problem. Any help would be appreciated.

答案1

得分: 0

以下是您要翻译的内容：

根据下面的演示，似乎您所展示的没有任何问题，因此，要么

  • PRAGMA foreign_keys = ON; 不起作用，或者
  • 绑定的值不是有效的 id，或者
  • 您对子/父的概念有误（即页面的子级是块行）

关于 PRAGMA foreign_keys = ON; 不起作用的问题。考虑以下内容：

> 要在 SQLite 中使用外键约束，库必须没有定义 SQLITE_OMIT_FOREIGN_KEY 和 SQLITE_OMIT_TRIGGER。
> 如果定义了 SQLITE_OMIT_TRIGGER，但未定义 SQLITE_OMIT_FOREIGN_KEY，
> 那么 SQLite 的行为与版本 3.6.19（2009-10-14）之前的版本相同 -
> 外键定义会被解析，并可以使用 PRAGMA foreign_key_list 进行查询，但不会强制执行外键约束。
> 在此配置中，PRAGMA foreign_keys 命令是一个无操作。如果定义了 OMIT_FOREIGN_KEY，
> 那么甚至无法解析外键定义（尝试指定外键定义会导致语法错误）。请参阅 https://www.sqlite.org/foreignkeys.html#fk_enable

工作演示

以下演示了您的删除的影响，可以用作测试的基础：

/* 以防清理 /
DROP TABLE If EXISTS pages;
DROP TABLE IF EXISTS blocks;
DROP TABLE IF EXISTS users;
/ 打开外键（反映状态 1、2 和 3 的结果） /
PRAGMA foreign_keys;
PRAGMA foreign_keys = off; / 以显示打开和关闭之间的区别） /
PRAGMA foreign_keys;
PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;
/ 创建表 /
CREATE TABLE IF NOT EXISTS users (
id TEXT PRIMARY KEY
);
CREATE TABLE
IF NOT EXISTS "pages" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"title" TEXT,
"author" TEXT REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
"creation_date" DATE,
"publication_date" DATE
);
CREATE TABLE
IF NOT EXISTS "blocks"(
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"page_id" INTEGER REFERENCES pages(id) ON DELETE CASCADE ON UPDATE CASCADE,
"type" TEXT,
"content" TEXT,
"position" INTEGER
);
/ 使用一些测试数据填充表 /
INSERT INTO users VALUES ('user1'),('user2'),('user3');
INSERT INTO pages VALUES
(1,'page1title','user1',datetime('now'),datetime('1984-01-01'))
,(2,'page2title','user1',datetime('now','+1 months'),datetime('1992-01-01'))
,(3,'page3title','user2',datetime('now','+5 weeks'),datetime('1970-01-01'))
,(4,'page4title','user3',datetime('now','+100 days'),datetime('2010-01-01'))
,(5,'page5title','user1',datetime('now','+2 years'),datetime('2003-01-01'))
,(6,'page6title','user3',datetime('now','+2 months','+3 years','+4 days'),datetime('2015-01-01'))
;
INSERT INTO blocks VALUES
(1,1,'TYPE1','BLAH1',1)
,(2,1,'TYPE2','BLAH2',2)
,(3,1,'TYPE2','BLAH3',3)
,(4,6,'TYPE3','BLAH4',1)
,(5,6,'TYPE4','BLAH5',2)
,(6,6,'TYPE1','BLAH6',3)
,(7,4,'TYPE3','BLAH7',2)
,(8,4,'TYPE2','BLAH8',2)
;
/ 显示插入的核心数据 - 结果 4 /
SELECT * FROM pages
JOIN users ON pages.author = users.id
JOIN blocks ON blocks.page_id = pages.id
;
/ 删除单个页面 /
DELETE FROM pages WHERE id = 4;
/ 显示删除后的数据 - 结果 5 /
SELECT * FROM pages
JOIN users ON pages.author = users.id
JOIN blocks ON blocks.page_id = pages.id
;
/ 显示块，因为块的 id 为 7 和 8 的应该已通过传播被删除 - 结果 6 /
SELECT * FROM blocks;
/ 清理测试环境 */
DROP TABLE If EXISTS pages;
DROP TABLE IF EXISTS blocks;
DROP TABLE IF EXISTS users;

结果

  • 结果 1 foreign_keys 1
  • 结果 2 foreign_keys 0
  • 结果 3 foreign_keys 1
  • 结果 4（初始数据）：SQLite的ON DELETE CASCADE约束未正常工作。
  • 结果 5（删除 id 为 4 的页面后）：SQLite的ON DELETE CASCADE约束未正常工作。
  • 结果 6（块表中的所有剩余行）：SQLite的ON DELETE CASCADE约束未正常工作。
  • 即删除 id 为 4 的页面导致相关的块行通过传播被删除。
英文:

As the demo below shows there appears to be nothing wrong with what you have shown so, Either

  • the PRAGMA foreign_keys = ON; is not working or,
  • the bound value is not a valid id or
  • you misunderstand what a child/parent is (i.e. the children of pages are the blocks rows)

In regard to the PRAGMA foreign_keys = ON; not working. Consider the following:-

> In order to use foreign key constraints in SQLite, the library must be
> compiled with neither SQLITE_OMIT_FOREIGN_KEY nor SQLITE_OMIT_TRIGGER
> defined.
>If SQLITE_OMIT_TRIGGER is defined but SQLITE_OMIT_FOREIGN_KEY
> is not, then SQLite behaves as it did prior to version 3.6.19
> (2009-10-14) - foreign key definitions are parsed and may be queried
> using PRAGMA foreign_key_list, but foreign key constraints are not
> enforced. The PRAGMA foreign_keys command is a no-op in this
> configuration. If OMIT_FOREIGN_KEY is defined, then foreign key
> definitions cannot even be parsed (attempting to specify a foreign key
> definition is a syntax error). see https://www.sqlite.org/foreignkeys.html#fk_enable

Working Demo

The following demonstrates the affect of your deletion and could be used as the basis of a test:-

  1. /* Just in case cleanup */
  2. DROP TABLE If EXISTS pages;
  3. DROP TABLE IF EXISTS blocks;
  4. DROP TABLE IF EXISTS users;
  5. /* Turn Foreign Keys on (reflecting the status Results 1, 2 and 3) */
  6. PRAGMA foreign_keys;
  7. PRAGMA foreign_keys = off; /* to show the difference between on and off)*/
  8. PRAGMA foreign_keys;
  9. PRAGMA foreign_keys = ON;
  10. PRAGMA foreign_keys;
  11. /* Create the tables */
  12. CREATE TABLE IF NOT EXISTS users (
  13. 	id TEXT PRIMARY KEY
  14. 	);
  15. CREATE TABLE
  16.     IF NOT EXISTS "pages" (
  17.         "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  18.         "title" TEXT,
  19.         "author" TEXT REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
  20.         "creation_date" DATE,
  21.         "publication_date" DATE
  22.     );
  23. CREATE TABLE
  24.     IF NOT EXISTS "blocks"(
  25.         "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  26.         "page_id" INTEGER REFERENCES pages(id) ON DELETE CASCADE ON UPDATE CASCADE,
  27.         "type" TEXT,
  28.         "content" TEXT,
  29.         "position" INTEGER
  30.     );
  31. /* Populates the tables with some test data */
  32. INSERT INTO users VALUES ('user1'),('user2'),('user3');
  33. INSERT INTO pages VALUES 
  34. 	(1,'page1title','user1',datetime('now'),datetime('1984-01-01'))
  35. 	,(2,'page2title','user1',datetime('now','+1 months'),datetime('1992-01-01'))
  36. 	,(3,'page3title','user2',datetime('now','+5 weeks'),datetime('1970-01-01'))
  37. 	,(4,'page4title','user3',datetime('now','+100 days'),datetime('2010-01-01'))
  38. 	,(5,'page5title','user1',datetime('now','+2 years'),datetime('2003-01-01'))
  39. 	,(6,'page6title','user3',datetime('now','+2 months','+3 years','+4 days'),datetime('2015-01-01'))
  40. ;
  41. INSERT INTO blocks VALUES
  42. 	(1,1,'TYPE1','BLAH1',1)
  43. 	,(2,1,'TYPE2','BLAH2',2)
  44. 	,(3,1,'TYPE2','BLAH3',3)
  45. 	,(4,6,'TYPE3','BLAH4',1)
  46. 	,(5,6,'TYPE4','BLAH5',2)
  47. 	,(6,6,'TYPE1','BLAH6',3)
  48. 	,(7,4,'TYPE3','BLAH7',2)
  49. 	,(8,4,'TYPE2','BLAH8',2)
  50. ;
  51. /* Show the core data that has been inserted - Result 4*/
  52. SELECT * FROM pages 
  53. 	JOIN users ON pages.author = users.id
  54. 	JOIN blocks ON blocks.page_id = pages.id
  55. ;
  56. /* Delete a single page */
  57. DELETE FROM pages WHERE id = 4;
  58. /* Show the data after the deletion - Result 5*/
  59. SELECT * FROM pages 
  60. 	JOIN users ON pages.author = users.id
  61. 	JOIN blocks ON blocks.page_id = pages.id
  62. ;
  63. /* Show the blocks, as blocks with id 7 and 8 should have been deleted through propogation - Result 6*/
  64. SELECT * FROM blocks;
  65. /* Cleanup the test environment */
  66. DROP TABLE If EXISTS pages;
  67. DROP TABLE IF EXISTS blocks;
  68. DROP TABLE IF EXISTS users;

Results

  • Result 1 foreign_keys 1

  • Result 2 foreign_keys 0

  • Result 3 foreign_keys 1

  • Result 4 (Initial data):-

  • SQLite的ON DELETE CASCADE约束未正常工作。

  • Result 5 (after delete of pages with id 4):-

  • SQLite的ON DELETE CASCADE约束未正常工作。

  • Result 6 (all remaining rows from the blocks table):-

  • SQLite的ON DELETE CASCADE约束未正常工作。

    • i.e. the deletion of the pages row with an id of 4 has resulted in the related blocks rows being deleted via propagation

huangapple
  • 本文由 发表于 2023年6月13日 00:50:10
  • 转载请务必保留本文链接：https://go.coder-hub.com/76458752.html
  • database
  • sql
  • sqlite
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定