Error removing column Rails

Question

In my Rails app, I have a model called Rutinas. After some time, I needed to add some columns to the table so I generated a migration 20171116094810_add_votos_y_veces_asignada_to_rutinas.rb:

class AddVotosYVecesAsignadaToRutinas < ActiveRecord::Migration[5.1]
  def change
    add_column :rutinas, :votos_pos, :integer, :default => 0
    add_column :rutinas, :votos_neg, :integer, :default => 0
    add_column :rutinas, :veces_asig, :integer, :default => 0
  end
end

After some other migrations, I needed to delete two columns that I don't need anymore votos_pos and votos_neg so I generated another migration 20171117092026_remove_votos_from_rutinas.rb:

class RemoveVotosFromRutinas < ActiveRecord::Migration[5.1]
  def change
    remove_column :rutinas, :votos_pos, :integer
    remove_column :rutinas, :votos_neg, :integer
  end
end

The problem is that when I run rails db:migrate to migrate this last migration, it throws some weird error:

== 20171117092026 RemoveVotosFromRutinas: migrating ===========================
-- remove_column(:rutinas, :votos_pos)
rails aborted!
StandardError: An error has occurred, this and all later migrations canceled:

SQLite3::ConstraintException: FOREIGN KEY constraint failed: DROP TABLE "rutinas"
C:/Users/pepe/Dropbox/pepe/KeepMeFit/KeepMeFit-git/db/migrate/20171117092026_remove_votos_from_rutinas.rb:3:in `change'
bin/rails:4:in `require'
bin/rails:4:in `<main>'

Caused by:
ActiveRecord::InvalidForeignKey: SQLite3::ConstraintException: FOREIGN KEY constraint failed: DROP TABLE "rutinas"
C:/Users/pepe/Dropbox/pepe/KeepMeFit/KeepMeFit-git/db/migrate/20171117092026_remove_votos_from_rutinas.rb:3:in `change'
bin/rails:4:in `require'
bin/rails:4:in `<main>'

Caused by:
SQLite3::ConstraintException: FOREIGN KEY constraint failed
C:/Users/pepe/Dropbox/pepe/KeepMeFit/KeepMeFit-git/db/migrate/20171117092026_remove_votos_from_rutinas.rb:3:in `change'
bin/rails:4:in `require'
bin/rails:4:in `<main>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

My Rutinas model is the following:

class Rutina < ActiveRecord::Base    
  validates_presence_of :nombre

  belongs_to :user
  has_many :repeticions
  has_many :entrenos, dependent: :destroy
  has_many :votos

  has_many :days, dependent: :destroy

  def get_array_dias
    (1..self.repeticions.last.dia).to_a
  end

  def get_number_of_days
    days.count
  end

end

User, repeticion, entreno, voto and day are other tables which are non-related to the columns that I'm trying to delete. That is to say, these columns are not a foreign key and any foreign key references these columns.

can you give a brief overview of your Rutinas model? You Rutinas model might have associated with another model. — current_user
– current_user, Commented Nov 17, 2017 at 9:43
Probably another model has foreign key on Rutinas model. Drop foreign key column in associated model before. — Mikhail Katrin
– Mikhail Katrin, Commented Nov 17, 2017 at 9:47
Can you go to rails db and paste output from command \d rutinas? — AntonTkachov
– AntonTkachov, Commented Nov 17, 2017 at 10:12

skahlert · Accepted Answer · 2017-11-17 12:01:13Z

2

It seems like the problem is the limitation of SQLite. If you check the documentation you'll see, that it only allows adding a column, but not removing one: https://sqlite.org/lang_altertable.html

It is possible, that the migration actually drops the whole table and recreates it, when a column is removed. This would explain the DROP TABLE "rutinas" message. Of course if the while table is dropped, it would make sense that certain foreign key constraints fail.

answered Nov 17, 2017 at 12:01

skahlert

6474 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

skahlert Over a year ago

After checking out the sqlite3_adapter.rb in the active record gem, it looks like the table is copied before dropping, so no data is lost. If you satisfy all the constraints by adding dependent: :destroy or dependent: :nullify to all the has_many statements the migration might work.

yahonda · Accepted Answer · 2018-05-23 10:40:15Z

2

It will be fixed in Rails 6 including https://github.com/rails/rails/pull/32865 . This requires SQLite database 3.8 and higher. I do not think it will be back ported into Rails 5.2 or older.

My answer may not give you any practical feedback how to write your code, what I can say now is upgrading Rails 6 (release date is not fixed) may resolve this problem.

edited May 23, 2018 at 10:40

answered May 23, 2018 at 2:17

yahonda

312 bronze badges

Collectives™ on Stack Overflow

Error removing column Rails

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related