How do i roll back this rails migration?

Question

I ran the below migration and forgot to include the default value I wanted. I'm trying to roll it back so that I can add the default and roll it forward again.

20190728151635_add_cooldown_to_skill_levels.rb

class AddCooldownToSkillLevels < ActiveRecord::Migration[5.1]
  def change
    add_column :skill_levels, :cooldown, :integer
  end
end

All rails db:migrate and db:rollbacks are failing with the below error

$ rails db:rollback

== 20190728151635 AddCooldownToSkillLevels: reverting =========================
-- remove_column(:skill_levels, :cooldown, :integer)
rails aborted!
StandardError: An error has occurred, this and all later migrations canceled:

SQLite3::ConstraintException: FOREIGN KEY constraint failed: DROP TABLE "skill_levels"



And 100+ more lines ...

I've tried changing the migration to have explicit up/down methods to no avail

class AddCooldownToSkillLevels < ActiveRecord::Migration[5.1]
  def up
    add_column :skill_levels, :cooldown, :integer
  end

  def down
    remove_column :skill_levels, :cooldown
  end
end

I've tried adding a new migration that just adds the default value.

20190728153208_add_default_value_to_cooldown_on_skill_levels.rb

class AddDefaultValueToCooldownOnSkillLevels < ActiveRecord::Migration[5.1]
  def change
    change_column_default :skill_levels, :cooldown, 1
  end
end

Similar error $ rails db:migrate

== 20190728153208 AddDefaultValueToCooldownOnSkillLevels: migrating ===========
-- change_column_default(:skill_levels, :cooldown, 1)
rails aborted!
StandardError: An error has occurred, this and all later migrations canceled:

SQLite3::ConstraintException: FOREIGN KEY constraint failed: DROP TABLE "skill_levels"

And 100+ more lines ...

I'm comfortable fixing it directly in SQL, but I'm trying to keep my migrations intact.

edit: adding output of $ rails db:migrate:status

...
   up     20190727160901  Create skill levels
   up     20190728004535  Create skill effects
   up     20190728151635  Add cooldown to skill levels
  down    20190728153208  Add default value to cooldown on skill levels

Appreciate your help.

Answer 1

The error:

FOREIGN KEY constraint failed: DROP TABLE "skill_levels" 

is telling you that there is a foreign key constraint that references your skill_levels table (i.e. t.references :skill_levels, foreign_key: true in some other migration) and the remove_column :skill_levels, :cooldown is causing a problem with that.

But why would a database constraint be a problem during an ALTER TABLE to remove a column? Well, SQLite's ALTER TABLE doesn't support removing a column, instead you have to (see the SQLite FAQ or How to delete or add column in SQLITE?):

1. Create a new table without the column that you're removing.
2. Copy the data to the new table.
3. Drop the original.
4. Rename the copy.

ActiveRecord does this behind the scenes for you. Step (3) is where things are going wrong because you can't drop a table that is being referenced by foreign key constraints. I think you'll have handle the foreign key by hand: first use separate up and down methods, then modify the down method to:

1. Drop all the foreign keys that reference your table, you can use remove_foreign_key for this once you know what FKs you're dealing with.
2. Then remove_column :skill_levels, :cooldown as you have now.
3. And finally put all the FKs back, you can use add_foreign_key for this.

Something like:

def down
  remove_foreign_key :some_table, :skill_levels
  remove_column :skill_levels, :cooldown
  add_foreign_key :some_table, :skill_levels
end

Ideally ActiveRecord would take care of this for you but presumably AR's SQLite support is mostly from before AR supported foreign key constraints.