执行 SQLite 更改表技巧时更新外键引用
在 SQLite FAQ[1] 中提到 SQLite 没有完整的 ALTER TABLE 支持。在 StackOverflow [2] 的上一个问题中，提到了完成表修改的技巧。
我想知道的是如何保留外键引用，因为这些引用被移动到随后被删除的重命名表。我是否应该对与我正在修改的实际表有外键关系的每个表执行相同的技巧？
In the SQLite FAQ[1] it is mentioned that SQLite does not have full ALTER TABLE support. In a previous question on StackOverflow [2] a trick is mentioned to accomplish table modifications.
What I would like to know is how to keep FOREIGN KEY references as these are moved to the renamed table which is subsequently deleted. Should I do the same trick with each and every table that has a foreign key relationship with the actual table I am modifying?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
是的，你需要做同样的“技巧”。当您重命名引用的表时，外键约束仍然以其新名称引用它。由于 SQLite 不支持“DROP CONSTRAINT”，因此您还必须使用更正的外键约束重建引用表。
事实上，在更正外键引用之前，您将无法删除旧表。只要
PRAGMAforeign_keys=ON;，SQLite3就不会让你删除仍然有外键引用它的表。Yes, you'll need to do the same "trick". When you rename the referenced table, foreign key constraints still refer to it under its new name. Since SQLite doesn't support "DROP CONSTRAINT", you'll have to rebuild the referencing tables with the corrected foreign key constraints, too.
In fact, you won't be able to drop the old table until you correct the foreign key references. As long as
PRAGMA foreign_keys=ON;, SQLite3 won't let you drop a table that still has foreign keys referencing it.对于当前版本的 SQLite，当使用
PRAGMAforeign_keys=OFF禁用外键处理时，不会重写其他表中的引用约束。不能说这种行为在 2011 年是否有所不同。
With current versions of SQLite referencing constraints in other tables are not rewritten when foreign key handling has been disabled using
PRAGMA foreign_keys=OFF.Can't say if that behavior was different in 2011.
对我有帮助的技巧：
在此序列之后，外键指向
some而不是some_oldThe trick that helped me:
After this sequence foreign keys are pointing to
someand not tosome_old