Removing/ Rewriting All Indexes in Rails Migration
I had a situation today where I had to sort out a mess of indexes. I wanted to clear all current indexes on our has_and_belongs_to_many (or has_many through) join tables and add only the composite unique index on the foreign keys.
I found that ActiveRecord has an IndexDefinition type struct and that
ActiveRecord::Base.connection.indexes(:table_name)
The next problem was that I'm adding a unique key to a table which didn't previously have one, so there could be all kinds of mess in that table, duplicate rows and such, so we need to clear that. I found another SO post which I modified a bit to come up with the migration code:
# Get rid of duplicates rows that will stop us adding our index execute "CREATE TABLE #{t}2 LIKE #{t}" execute "INSERT INTO #{t}2 SELECT * FROM #{t} GROUP BY #{c.join(", ")}" execute "DROP TABLE #{t}" execute "RENAME TABLE #{t}2 TO #{t}"
So, using these techniques, my complete migration looked something like:
class AddIndexesOnJoinTables < ActiveRecord::Migration def change tables_columns = { table1: [:foreign_key_1, :foreign_key_2], table2: [:foreign_key_3, :foreign_key_4], table3: [:foreign_key_5, :foreign_key_6], table4: [:foreign_key_7, :foreign_key_8] } tables_columns.each do |t,c| # Get rid of duplicates rows that will stop us adding our index execute "CREATE TABLE #{t}2 LIKE #{t}" execute "INSERT INTO #{t}2 SELECT * FROM #{t} GROUP BY #{c.join(", ")}" execute "DROP TABLE #{t}" execute "RENAME TABLE #{t}2 TO #{t}" # Get existing indexes and remove them indices = indexes(t) indices.each do |i| remove_index(t, i.columns) end # Add our new one begin add_index(t, c, unique: true) rescue ArgumentError => e if e.message.include? "the limit is 64 characters" add_index(t,c, unique: true, name: Time.now.to_i) end end end end end
The only other addition I haven't mentioned yet is the little begin rescue end block. For whatever reason, Rails and the mysql2 adapter still generate and try to add indexes with names that are far too long, resulting in the exception:
An error has occurred, all later migrations canceled: #<ArgumentError: Index name 'index_medical_conditions_students_on_student_id_and_medical_condition_id' on table 'medical_conditions_students' is too long; the limit is 64 characters>
The solution is to pass the add_index method a name parameter, but seeing as we're generating all our indexes in a loop, this is a pain in the arse. We'll either need to name them all, or introduce some extra logic. I didn't want to do either of those so I fudged it a little. The rescue block catches the ArgumentError, checks that it's because of our index length (by looking at the message) and if so, adds the index again using a timestamp as the index name (to avoid conflicts). Not perfect by any means but seems to do the job for me.
Tidy!