When renaming column in Users table getting: SQLite3::ConstraintException: FOREIGN KEY constraint failed: DROP TABLE "users"

Question

I am working on a Rails app with Sqlite and have a users table associated with several other tables. When trying to rename a column in Users, I'm getting the subject error when running rails db:migrate.

I see a lot of posts here with similar issues, but none has worked. Specifically, the common remedy seems to be to use "dependent: :destroy" on all has_many and has_one associations. I am doing this but am still getting the error.

What am I doing wrong?

Below is my code:

class User < ApplicationRecord
  devise :database_authenticatable, :registerable,
  :recoverable, :rememberable, :trackable, :validatable

  has_one :profile, dependent: :destroy
  has_many :bikes, dependent: :destroy
  has_many :bookings, dependent: :destroy
  has_many :rented_bikes, through: :bookings, source: :bike
  has_many :conversations, dependent: :destroy
  has_many :likes, dependent: :destroy
  has_many :liked_bikes, through: :likes, :source => :bike
  has_many :viewed_bikes, through: :views, :source => :bike
  has_many :views, dependent: :destroy
  has_many :reviews, dependent: :destroy
end

class Profile < ApplicationRecord
  belongs_to :user
end

class Bike < ApplicationRecord
  belongs_to :user
  has_many :images, dependent: :destroy
  has_many :bookings, dependent: :destroy
  has_many :booked_users, through: :bookings, source: :user
  has_many :conversations, dependent: :destroy
  has_many :likes, dependent: :destroy
  has_many :liking_users, :through => :likes, :source => :user
  has_one :amenity, dependent: :destroy
  has_many :places, dependent: :destroy
  has_many :views, dependent: :destroy
end

class Booking < ApplicationRecord
  belongs_to :bike
  belongs_to :user

  has_one :review, dependent: :destroy

  validates :date_start, presence: true
  validates :date_end, presence: true
  validates :user_id, presence: true
end

class Conversation < ApplicationRecord
  belongs_to :user
  belongs_to :bike

  has_many :messages, dependent: :destroy
end

class Like < ApplicationRecord
  belongs_to :user
  belongs_to :flat
end

class View < ApplicationRecord
  belongs_to :user
  belongs_to :flat
end

class Review < ApplicationRecord
  belongs_to :user
  belongs_to :booking
end

Migration:

class ChangeCustomerIdToUserId < ActiveRecord::Migration[5.1]
  def change
    rename_column :users, :customer_id, :client_id
  end
end

Answer 1

You have a couple problems happening at once:

1. SQLite doesn't support renaming columns so the ActiveRecord driver implements column renaming the hard way: create a new table with the new column names, copy all the data, drop the original table, rename the new one. Note that this has recently changed so the latest SQLite does support renaming columns in-place.
2. You have foreign keys in other tables that reference your users table.

(2) is what is triggering your error during your migration: you can't drop a table (see (1)) when there are foreign keys referencing it since dropping the table would violate those foreign keys.

The solution is to drop all the offending FKs in your migration, then do the rename_column, and then add all the FKs back again. Another option would be to try to turn off FKs and turn them back on in your migration, something like:

connection.execute("PRAGMA defer_foreign_keys = ON")
connection.execute("PRAGMA foreign_keys = OFF")
rename_column :users, :customer_id, :client_id
connection.execute("PRAGMA foreign_keys = ON")
connection.execute("PRAGMA defer_foreign_keys = OFF")

might work.

---

There was a commit made to Rails three months ago that should fix this problem but I don't think it has made it into any release version yet.