Rails迁移-暂时忽略外键约束？

Question

我想将表格的id字段更改为uuid。

以下是我的代码：

class AddUuidToProjects < ActiveRecord::Migration[5.0]
  def up
    add_column :projects, :uuid, :string, limit:36, null: false, first: true
    add_column :projects, :old_id, :integer

    Project.all.each do |p|
      p.update!(old_id: p.id)
    end
    change_table :projects do |t|
      t.remove :id
      t.rename :uuid, :id
    end
    execute "ALTER TABLE projects ADD PRIMARY KEY (id);"

    Project.all.each do |p|
      # has_one image
      Image.find(p.old_id).update!(project: p) 
      # has_many stories
      Story.where(project_id: p.old_id).each do |s|
        s.update!(project: p)
      end
    end
  end
  ...
end

当尝试使用t.remove :id时，此迁移会因外键约束而中断。错误消息如下：

Mysql2::Error: Cannot drop column 'id': needed in a foreign key constraint 'fk_rails_be41fd4bb7' of table 'db_dev.stories': ALTER TABLE `projects` DROP `id`

事实上，如果整个迁移都运行了，那么我将用另一个列替换id列，并修复外键。所以，有没有办法在迁移时忽略约束条件呢？

- Mirror318

---

尝试先删除约束，然后稍后再添加它。https://dev59.com/P2Yq5IYBdhLWcg3w4Edx - jvnill

你也可以将 uuid 设为主键，并让 Active Record 知道这一点。http://api.rubyonrails.org/classes/ActiveRecord/AttributeMethods/PrimaryKey/ClassMethods.html#method-i-primary_key - jvnill

我会考虑创建覆盖测试的单独数据迁移。更多信息请参见 https://robots.thoughtbot.com/data-migrations-in-rails。 - Artur INTECH

Answer 1

Project.connection.disable_referential_integrity do
  Project.delete_all # An example
end

ActiveRecord::Base.connection.disable_referential_integrity。

Rails 4.2+。

- Artur INTECH

---

还要注意，在传递给此函数的块内进行模式更改，因此 ActiveRecord :: Base.connection.disable_referential_integrity do ... end，这样即使迁移失败，任何FK强制执行更改也会被回滚。 - Rich Sutton

Answer 2

在 MySQL 中，您可以这样做：

begin
  ActiveRecord::Base.connection.execute 'SET FOREIGN_KEY_CHECKS=0;'
  # Your statements
  ...
ensure
  ActiveRecord::Base.connection.execute 'SET FOREIGN_KEY_CHECKS=1;'
end

- Benj

Answer 3

我的最终代码

if和unless语句是因为我逐步编写和测试（失败的迁移仍会产生持久影响）。主要是在最后删除外键，然后再添加回来（删除键不会删除数据库中的id字段，只会删除限制条件）。

class AddUuidToProjects < ActiveRecord::Migration[5.0]
  def up
    # remove constraint
    if foreign_key_exists?(:stories, :projects)
      say("removing foreign key constraints")

      remove_foreign_key "stories", "projects"
      remove_foreign_key "images", "projects"
    end

    # create UUID id column
    unless column_exists?(:projects, :id, :string)
      say("adding UUID column")

      add_column :projects, :uuid, :string, limit:36, null: false, first: true
      add_column :projects, :old_id, :integer

      Project.all.each do |p|
        p.update!(old_id: p.id, uuid: SecureRandom.uuid)
      end

      change_table :projects do |t|
        t.remove :id
        t.rename :uuid, :id
      end
      execute "ALTER TABLE projects ADD PRIMARY KEY (id);"
    end

    # update foreign keys
    if(Image.first.project_id.is_a? Integer)
      say("updating foreign keys")

      # change foreign key fields to STRING(36)
      change_column :images, :project_id, :string, limit:36, null: false
      change_column :stories, :project_id, :string, limit:36, null: false

      Project.all.each do |p|
        # has_one soi
        Image.find_by(project: p.old_id).update!(project: p)

        # has_many stories
        Snippet.where(project_id: p.old_id).each do |s|
          s.update!(project: p)
        end
      end
    end

    # add constraints back
    unless foreign_key_exists?(:stories, :projects)
      say("adding foreign key constraints back")

      add_foreign_key "stories", "projects"
      add_foreign_key "images", "projects"
    end
  end

- Mirror318

Answer 4

如果你的环境同时混合了mysql和sqlite，你可以这样做：

class AddUuidToProjects < ActiveRecord::Migration[5.0]
  def up
    ActiveRecord::Base.connection.execute 'SET FOREIGN_KEY_CHECKS=0;' if ActiveRecord::Base.connection.adapter_name == 'Mysql2'
    # Your code
    # ...
    ActiveRecord::Base.connection.execute 'SET FOREIGN_KEY_CHECKS=1;' if ActiveRecord::Base.connection.adapter_name == 'Mysql2'
  end

  def down
    ActiveRecord::Base.connection.execute 'SET FOREIGN_KEY_CHECKS=0;' if ActiveRecord::Base.connection.adapter_name == 'Mysql2'
    # Your code
    # ...
    ActiveRecord::Base.connection.execute 'SET FOREIGN_KEY_CHECKS=1;' if ActiveRecord::Base.connection.adapter_name == 'Mysql2'
  end
end

- jing