Home>

In Ruby on Rails, when I tried to delete a column called place from a table called problems and executed a migration file, the following error occurred.

Error message
C: \ Users \ XXXX>rails db: migrate
== 20180819025741 RemovePlaceToProblem: migrating =============================
-remove_column (: problems,: place,: string)
rails aborted!
StandardError: An error has occurred, this and all later migrations canceled:
SQLite3 :: ConstraintException: FOREIGN KEY constraint failed: DROP TABLE "problems"
C: /Users/XXXX/db/migrate/20180819025741_remove_place_to_problem.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 "problems"
C: /Users/XXXX/db/migrate/20180819025741_remove_place_to_problem.rb: 3: in `change '
bin/rails: 4: in `require '
bin/rails: 4: in `<main>'
Caused by:
SQLite3 :: ConstraintException: FOREIGN KEY constraint failed
C: /Users/XXXX/db/migrate/20180819025741_remove_place_to_problem.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)
Applicable source code

(↓) db \ migrate \ 20180819025741_remove_place_to_problem.rb

class RemovePlaceToProblem<ActiveRecord :: Migration [5.1]
  def change
    remove_column: problems,: place,: string
  end
end


(↓) db \ schema.rb

create_table "problems", force:: cascade do | t |
    t.string "name"
    t.string "grade"
    t.string "place"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end
  create_table "posts", force:: cascade do | t |
    t.text "URL"
    t.text "comment"
    t.integer "problem_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.boolean "approved"
    t.index ["problem_id"], name: "index_posts_on_problem_id"
  end


(↓) app \ models \ problem.rb

class Problem<ApplicationRecord
  has_many: posts, dependent:: destroy
  validates: name, presence: true, uniqueness: true
  validates: grade, presence: true
end


(↓) app \ models \ post.rb

class Post<ApplicationRecord
  belongs_to: problem, dependent: destroy
  validates: URL, presence: true, uniqueness: true
  validates: comment, length: {maximum: 500}
end


I read the error message and found that an error related to the foreign key constraint seems to have occurred, but I don't know how to delete it without causing an error.

  • Answer # 1

    SQLite does not implement operations to rename or delete columns.
    So Rails tries to recreate the entire table.

    may not have been able to delete problems due to foreign key constraints.

    bin/rake db: migrate: reset
    Since the data is emptied and migrated again, the column can be deleted (although the table is actually recreated) without applying foreign key constraints.

    If you want to save data, SQLite has an operation to rename a table, so
    Manually

    Create a new table problems2 with no columns you want to delete
    Copy data from problems to problems2
    Rename problems to problems_old
    Rename problems2 to problems
    Drop problems2

    It may be possible by something like

    .
    (However, there is a possibility that an operation such as removing FK and re-adding it in the middle is necessary. It is subtle)