1

I am developing a Rails web app. I have a table called countries that is associated with two other tables: cities and sites. When I try to change the column code to name in the countries table, I get the following exception:

ActiveRecord::InvalidForeignKey: SQLite3::ConstraintException: FOREIGN KEY constraint failed: DROP TABLE "countries"

I have tried adding the dependent: :destroy thing, but I still get the same exception. I tried dropping the table, and recreating it, and still not working. Any help would be greatly appreciated.

Country model:

class Country < ApplicationRecord

    has_many :cities, dependent: :destroy
    has_many :sites, dependent: :destroy

    has_many_attached :photos

    validates :code, uniqueness: true
    validates :about, presence: true

end

Here is the migration code I'm using to change the name of the column in the countries table:

class FixCodeName < ActiveRecord::Migration[5.2]
def change
    rename_column :countries, :code, :name
  end
end
Mahmoud Mousa Hamad
  • 538
  • 8
  • 21

1 Answers1

0

How are you attempting to rename the column? With a migration like the following?

class ChangeColumnName < ActiveRecord::Migration[5.2]
  def change
    rename_column :countries, :code, :name
  end
end

If you drop your whole database you can edit your older migrations and then recreate it. That's what I usually do if it's early in the development process.

InsolentWorm
  • 71
  • 1
  • 6
  • Yes, through a migration. I am in early stages of the development of my app, but I'm tired of dropping my who database every time something goes wrong or I want to make a small change. – Mahmoud Mousa Hamad Jul 26 '19 at 15:20
  • I don't understand why your code is trying to DROP TABLE "countries" to rename a column. Can you shed any light on this? – InsolentWorm Jul 26 '19 at 15:28
  • My code is not trying to DROP TABLE "countries", I am not sure why such alleged action is mentioned in the exception text. – Mahmoud Mousa Hamad Jul 26 '19 at 15:30
  • Can you edit your post and add your migration code to it? Maybe that will give us a clue. – InsolentWorm Jul 26 '19 at 15:31
  • I believe you're getting this error because there's an index on column code. Can you remove the index then try to run the migration? – InsolentWorm Jul 26 '19 at 15:38
  • How do I remove the index? – Mahmoud Mousa Hamad Jul 26 '19 at 15:39
  • To get the information for the table countries, use the following command from the sqlite command line: .schema countries, then DROP INDEX index_name. – InsolentWorm Jul 26 '19 at 15:42
  • You could also try to remove it in a migration by using remove_index :countries, column: :code – InsolentWorm Jul 26 '19 at 15:47
  • The migration was successful (and I assumed it removed the index, I'm not sure). But the column name change still returns the same exception. – Mahmoud Mousa Hamad Jul 26 '19 at 15:54
  • If you run out of ideas, I will just do what you suggested in the first place; to drop the entire database. – Mahmoud Mousa Hamad Jul 26 '19 at 15:55
  • I would fire up the sqlite cli to check if the index got removed. The clue I'm going off of is from the answer of this post https://stackoverflow.com/questions/6732896/does-rename-column-take-care-of-indexes. I recommend using a different database for development because Sqlite does not always behave like the other options. – InsolentWorm Jul 26 '19 at 15:58
  • @InsolentWorm old versions of sqlite didn't support renaming columns. The workaround involved copying data to a new table, dropping the original, and renaming the new one. This naturally doesn't play well with foreign keys referring to the column in question. Dealing with that is *really* ugly (see https://www.sqlite.org/lang_altertable.html). The ORM might not even attempt to try. – Shawn Jul 26 '19 at 19:29
  • Thank you for your help, but I solved it by deleting `fix_code_name` migration and then editing the migration that created the `countries` table then finally running `rails db:drop:_unsafe db:create db:migrate`. – Mahmoud Mousa Hamad Jul 26 '19 at 20:01
  • I've chosen your answer, but I will edit your answer to reflect what I've done. – Mahmoud Mousa Hamad Jul 26 '19 at 20:02