Fix foreign key issue in migration

[sekmo]

Hey, I've noticed that if we run the migrations generated by this gem using a MySQL database we get an error that prevents the migration from being run successfully. Specifically, when the AddMissingUniqueIndices migration runs, we try to remove the tag_id index from the taggings table, but it fails because we have the foreign key on tags(id).

I've encountered this problem with MySQL 5.7 (the most used nowadays), 8.0.32, and 8.0.33.

This is the error:

$ rake db:migrate
== 20230812090522 ActsAsTaggableOnMigration: migrating ========================
-- create_table(:tags, {})
   -> 0.0800s
-- create_table(:taggings, {})
   -> 0.0273s
-- add_index(:taggings, [:taggable_id, :taggable_type, :context], {:name=>"taggings_taggable_context_idx"})
   -> 0.0345s
== 20230812090522 ActsAsTaggableOnMigration: migrated (0.1421s) ===============

== 20230812090523 AddMissingUniqueIndices: migrating ==========================
-- add_index(:tags, :name, {:unique=>true})
   -> 0.0268s
-- index_exists?(:taggings, :tag_id)
   -> 0.0005s
-- remove_index(:taggings, :tag_id)
rake aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: Cannot drop index 'index_taggings_on_tag_id': needed in a foreign key constraint
/Users/francesco.mari/workspace-offline/shop/db/migrate/20230812090523_add_missing_unique_indices.acts_as_taggable_on_engine.rb:8:in `up'

Caused by:
ActiveRecord::StatementInvalid: Mysql2::Error: Cannot drop index 'index_taggings_on_tag_id': needed in a foreign key constraint
/Users/francesco.mari/workspace-offline/shop/db/migrate/20230812090523_add_missing_unique_indices.acts_as_taggable_on_engine.rb:8:in `up'

Caused by:
Mysql2::Error: Cannot drop index 'index_taggings_on_tag_id': needed in a foreign key constraint
/Users/francesco.mari/workspace-offline/shop/db/migrate/20230812090523_add_missing_unique_indices.acts_as_taggable_on_engine.rb:8:in `up'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

With this fix we remove the foreign key before the offending line, and add it again at the end, after we create the compound index.

[seuros]

I think you have added those indices throught schema.rb or some other way.

Can you reproduce it in a empty database/project ?

[sekmo]

mm but what's the point of trying on an empty DB? Gems are usually added to existing projects with populated DBs, no?

[seuros]

If the tagging tables/relations existed in the database, that mean the migrations will fail.

[barnaclebarnes]

This is an issue on a new DB as well (I just ran across it). It is because the previous migration does this:

    create_table ActsAsTaggableOn.taggings_table do |t|
      t.references :tag, foreign_key: { to_table: ActsAsTaggableOn.tags_table }

Which creates a foreign key which must be removed first.

I added the line as per this PR and it fixed it.