Rails: How to migrate without triggering foreign key checks (referential integrity checks)?

Question

I need to change the column type of all primary keys in all tables to the UUID format.

How do I migrate without triggering the foreign key checks?

I have tried the following 2 things below, but both still trigger the error:

Mysql2::Error: Cannot change column 'id': used in a foreign key constraint 'fk_rails_6010a70481' of table 'project_development.some_other_table'

.

class JustAnotherMigration < ActiveRecord::Migration[7.0]

  def change
    ActiveRecord::Base.connection.disable_referential_integrity do
      change_column :cities, :id, :uuid
    end
  end

end

.

class JustAnotherMigration < ActiveRecord::Migration[7.0]

  def change
    ActiveRecord::Base.connection.execute "SET FOREIGN_KEY_CHECKS=0;"
    change_column :cities, :id, :uuid
    ActiveRecord::Base.connection.execute "SET FOREIGN_KEY_CHECKS=1;"
  end

end

Rails version: 7.0.4.3

MariaDB version: 11.1.2

Answer 1

You have woefully underestimated how much of a ordeal changing the primary key type is. If you want to change the primary key type beyond just changing numerical types you have to actually update all the related tables first to avoid orphaning the records in thoses tables.

The outline of the process is:

1. Backup your database.
2. Create a cities.uiid UUID type column. Don't touch the id column for now.
3. Create a other_table.city_uuid column. It should be nullable and you can skip the foreign key constraint for now.
4. Fill the other_table.city_uuid with the cities.uiid. This can either be done with OtherModel.include(:city).find_each { |om| om.update(city_uuid: om.city.uiid) } or by using UPDATE ALL with a subquery or lateral join. The former is sufficient for small data sets and the later will be faster by an order of magintidue as it doesn't result in N+1 write queries.
5. Now that you're done recreating the relation between the tables remove the other_table.city_id column. This will take care of the foreign key restraint problem.
6. Remove cities.id. This may require you to remove the index first.
7. Rename cities.uuid to cities.id.
8. Rename other_table.city_uuid to other_table.city_id, add a foreign key constraint and make it non-nullable (if that's applicable).

The steps 3-5 must be repeated for every foreign key column in your schema.