Sqlite3 单表级联删除数据

最近在写一个网络相关的 iOS 库, 遇到一个需要级联删除数据的地方, 本来想用外键约束的级联删除来解决
后来发现, 在外键约束时 插入数据是有条件限制的, 在某些情况下插入数据会失败, 不过蛋疼之余还是给我想到解决方案了, 嘻嘻

外键约束

这里我不会讲外键约束是什么, 其他地方可以找到很多资料
这里只关注当前需要解决的问题

表结构

CREATE TABLE "test_table" (
  "id" INTEGER PRIMARY KEY,
  "uuid" TEXT,
  "parent_uuid" TEXT REFERENCES test_table(uuid) ON DELETE CASCADE
);

存在的问题

当插入数据时, 就会看到如下错误

INSERT INTO test_table (uuid, parent_uuid)VALUES('a', 'a');

-- foreign key mismatch - "test_table" referencing "test_table", Time: 0.003000s

根据外键约束的原理, 可以知道这就是一个先有鸡还是先有蛋的问题
因为 在插入数据时会检查parent_uuid中的值是否存在uuid列中, 如果不存在则插入失败

可行的方案

如果你的uuid是主键的并且在插入数据时满足以下两个条件的任意一个可以插入成功

1. parent_uuid为NULL
2. parent_uuid等于uuid

而实际情况则是多变的, 这样处理的话制约性太强了, 方案并不好
Ps: 虽然我当前的这个项目用的是在代码中递归实现的, 但是我还是要说下下面的这个方案
之后会改成这个方案😁

触发器

同样的在这里我不会叫触发器是什么, 资料其他地方很多

数据表还是上面的那个结构
下面是数据表中的数据(Ps: 因为是触发器实现所以不存在参入检查的问题)

id	uuid	parent_uuid
1	a	b
2	b	c
3	c	d

最终方案

下面是触发器Sql:

CREATE TRIGGER "tri_test_table" AFTER DELETE ON "test_table" 
BEGIN
	DELETE FROM test_table WHERE parent_uuid = old.uuid;
END

下面是删除删除数据Sql:

PRAGMA recursive_triggers = true;
delete from test_table where uuid = 'a';
PRAGMA recursive_triggers = false;

关于recursive_triggers

如果不开启的话, 触发器只会触发一次, 那么最终id=3这条记录仍然是会存在的, 这样效果级联删除的效果就没有达到了
但是 Sqlite3 默认是关闭 recursive_triggers 这个选项的, 需要手动开启, 具体的可以看下面参考资料的官方文档

参考资料

https://sqlite.org/pragma.html#pragma_recursive_triggers