在执行SQLite alter table技巧时更新外键引用 [英] Update foreign key references when doing the SQLite alter table trick

问题描述

在SQLite FAQ [1]中提到SQLite没有完整的ALTER TABLE支持。在上一个关于StackOverflow [2]的问题中提到了一个技巧来完成表格修改。

我想知道的是如何保持FOREIGN KEY引用到后来被删除的重命名表。我应该对每一个与我正在修改的实际表具有外键关系的表执行相同的技巧吗？
$ b [1] http://www.sqlite.org/lang_altertable.html

[2] 如何重命名列在SQLite数据库表中？

解决方案

是的，你需要做同样的技巧。当你重命名被引用的表时，外键约束仍然以它的新名字引用它。由于SQLite不支持DROP CONSTRAINT，所以你必须用正确的外键约束来重建引用表。实际上，你赢了'你可以删除旧表，直到你改正外键参考。只要 PRAGMA foreign_keys = ON; ，SQLite3不会让你删除一个仍然有外键引用它的表。

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?

[1] http://www.sqlite.org/lang_altertable.html

[2] How do I rename a column in a SQLite database table?

解决方案

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 alter table技巧时更新外键引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！