Rails migrations—temporarily ignore foreign key constraint?

Question

I'm trying to change a table's id field to be a uuid

Here's my code:

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

This migration breaks when trying t.remove :id, because of a foreign key constraint. The error message is:

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`

The thing is, if the whole migration ran then I would be swapping the id column out with another one, and fixing up the foreign keys too. So, is there any way to ignore the constraints for the migration?

try dropping the constraint first then adding it again later. http://stackoverflow.com/questions/14122031/how-to-remove-constraints-from-my-mysql-table — jvnill, Dec 07 '16 at 02:48
you can also just set uuid as the primary key and just let active record know that. http://api.rubyonrails.org/classes/ActiveRecord/AttributeMethods/PrimaryKey/ClassMethods.html#method-i-primary_key — jvnill, Dec 07 '16 at 02:49
I would consider creating separate data migration covered with tests. More on this https://robots.thoughtbot.com/data-migrations-in-rails. — Artur INTECH, Feb 09 '23 at 00:38

Artur INTECH · Accepted Answer · 2023-02-09T00:32:21.143

10

Project.connection.disable_referential_integrity do
  Project.delete_all
end

ActiveRecord::Base.connection.disable_referential_integrity.

Rails 4.2+.

edited Feb 09 '23 at 00:32

answered Dec 15 '18 at 18:04

Artur INTECH

6,024
2
37
34

Also note to make your schema changes inside a block passed to this function, so `ActiveRecord::Base.connection.disable_referential_integrity do ... end`, so that any FK enforcement change is rolled back even if your migration fails. – Rich Sutton Feb 08 '23 at 23:36

score 3 · Answer 2 · answered Oct 03 '18 at 06:44

3

On mysql, you can do this :

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

answered Oct 03 '18 at 06:44

Benjamin Bouchet

12,971
2
41
73

score 1 · Answer 3 · answered Dec 07 '16 at 04:19

My final code

The if and unless statements were there because I was writing and testing incrementally (and a failed migration still had lasting effects). The main thing is deleting the foreign keys then adding them back in the end (deleting the keys doesn't delete the id fields in the db, only the constraints.

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

score 0 · Answer 4 · answered Jul 08 '20 at 12:43

In case if you have mixed mysql - sqlite environment, you could do:

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

Rails migrations—temporarily ignore foreign key constraint?

4 Answers4