在创建 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.
相关文章