From 45881b0a64b4039e3b0d27e2c4d959dc2d744b9d Mon Sep 17 00:00:00 2001 From: Yasuo Honda Date: Mon, 7 May 2018 09:22:23 +0000 Subject: Disable foreign keys during `alter_table` for sqlite3 adapter Unlike other databases, changing SQLite3 table definitions need to create a temporary table. While changing table operations, the original table needs dropped which caused `SQLite3::ConstraintException: FOREIGN KEY constraint failed` if the table is referenced by foreign keys. This pull request disables foreign keys by `disable_referential_integrity`. Also `disable_referential_integrity` method needs to execute `defer_foreign_keys = ON` to defer re-enabling foreign keys until the transaction is committed. https://www.sqlite.org/pragma.html#pragma_defer_foreign_keys Fixes #31988 - This `defer_foreign_keys = ON` has been supported since SQLite 3.8.0 https://www.sqlite.org/releaselog/3_8_0.html and Rails 6 requires SQLite 3.8 #32923 now - .reset_column_information added to address `ActiveModel::UnknownAttributeError` ``` Error: ActiveRecord::Migration::ForeignKeyChangeColumnTest#test_change_column_of_parent_table: ActiveModel::UnknownAttributeError: unknown attribute 'name' for ActiveRecord::Migration::ForeignKeyChangeColumnTest::Post. ``` --- .../active_record/connection_adapters/sqlite3_adapter.rb | 15 ++++++++++----- 1 file changed, 10 insertions(+), 5 deletions(-) (limited to 'activerecord/lib/active_record/connection_adapters') diff --git a/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb b/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb index 32e2ed6fc6..544374586c 100644 --- a/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb @@ -187,13 +187,16 @@ module ActiveRecord # REFERENTIAL INTEGRITY ==================================== def disable_referential_integrity # :nodoc: - old = query_value("PRAGMA foreign_keys") + old_foreign_keys = query_value("PRAGMA foreign_keys") + old_defer_foreign_keys = query_value("PRAGMA defer_foreign_keys") begin + execute("PRAGMA defer_foreign_keys = ON") execute("PRAGMA foreign_keys = OFF") yield ensure - execute("PRAGMA foreign_keys = #{old}") + execute("PRAGMA defer_foreign_keys = #{old_defer_foreign_keys}") + execute("PRAGMA foreign_keys = #{old_foreign_keys}") end end @@ -407,9 +410,11 @@ module ActiveRecord caller = lambda { |definition| yield definition if block_given? } transaction do - move_table(table_name, altered_table_name, - options.merge(temporary: true)) - move_table(altered_table_name, table_name, &caller) + disable_referential_integrity do + move_table(table_name, altered_table_name, + options.merge(temporary: true)) + move_table(altered_table_name, table_name, &caller) + end end end -- cgit v1.2.3