创建sqlite3表后将其添加到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?

我的模式如下：

创建表技能（名称varchar，技能varchar，级别int，外键（名称）引用跑步者（名称），主键（名称，技能））；

如果外键被删除，我想层叠。

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 命令的文本副本，该副本在其 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表后将其添加到DELETE CASCADE行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！