在创建 sqlite3 表后，将 ON DELETE CASCADE 行为添加到它 [英] Add ON DELETE CASCADE behavior to an sqlite3 table after it has been created

问题描述

是否可以在创建表后将 ON DELETE CASCADE 添加到表中?

Is it possible to add an ON DELETE CASCADE to a table after it has been created?

我的架构如下:

CREATE TABLE Skills(name varchar, Skill varchar, level int,外键(name)引用runners(name),主键(name, Skill));

如果外键被删除，我想级联.

And I would like to cascade if the foreign key is deleted.

推荐答案

SQLite 的 ALTER TABLE命令不能做你想做的事.

SQLite's ALTER TABLE command cannot do what you want.

但是，可以绕过SQL解释器直接更改内部表定义.SQLite 在其 CREATE TABLE 命令的文本副本rel="noreferrer">sqlite_master 表；查看此查询的结果:

However, it is possible to bypass the SQL interpreter and change the internal table definition directly. SQLite stores table definitions as a textual copy of the CREATE TABLE command in its sqlite_master table; check out the result of this query:

SELECT sql FROM sqlite_master WHERE type='table' AND name='skills';

将您的级联规范添加到该字符串，然后使用 sqlite_master 的写访问">PRAGMA writable_schema=1; 并将您的新表定义写入其中:

Add your cascade specification to that string, then enable write access to sqlite_master with PRAGMA writable_schema=1; and write your new table definition into it:

UPDATE sqlite_master SET sql='...' WHERE type='table' AND name='skills';

然后重新打开数据库.

警告:这仅适用于不会更改表的磁盘格式的更改.如果您确实进行了更改记录格式的任何更改(例如添加/删除字段，或修改 rowid)，您的数据库将会爆炸.

WARNING: This works only for changes that do not change the on-disk format of the table. If you do make any change that changes the record format (such as adding/removing fields, or modifying the rowid), your database will blow up horribly.

这篇关于在创建 sqlite3 表后，将 ON DELETE CASCADE 行为添加到它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！