在创建 sqlite3 表后，将 ON DELETE CASCADE 行为添加到它-数据库问题

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

本文介绍了在创建 sqlite3 表后，将 ON DELETE CASCADE 行为添加到它的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

是否可以在创建表后将 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 行为添加到它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！