Rails 5.2 Error Changing or Removing Table Column (SQLite3::ConstraintException: FOREIGN KEY constraint failed: DROP TABLE)

Question

I'm trying to accomplish the fairly simple feat of changing the default value for one of the columns from my Blog table. I have the following migration:

class UpdateBlogFields < ActiveRecord::Migration[5.2]
  def change
    change_column :blogs, :freebie_type, :string, default: "None"
  end
end

Fairly simple, but I'm getting the following error when I run rake db:migrate:

StandardError: An error has occurred, this and all later migrations canceled:
SQLite3::ConstraintException: FOREIGN KEY constraint failed: DROP TABLE "blogs"

I get this error any time I try to change or remove a column, but not when adding one.

My schema looks like this:

  create_table "blogs", force: :cascade do |t|
    t.string "title"
    t.string "teaser"
    t.text "body"
    t.string "category", default: "General"
    t.string "linked_module"
    t.boolean "published", default: false
    t.datetime "published_on"
    t.integer "user_id"
    t.integer "image_id"
    t.integer "pdf_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.string "slug"
    t.string "cta_read_more", default: "Read More"
    t.string "cta_pdf", default: "Get My Free PDF"
    t.string "cta_video", default: "Watch the Video"
    t.string "convertkit_data_form_toggle"
    t.string "convertkit_href"
    t.integer "pin_image_id"
    t.string "data_pin_description"
    t.string "freebie_filename"
    t.string "video_link"
    t.string "freebie_type", default: "File"
    t.string "freebie_description"
    t.integer "comments_count"
    t.integer "subcategory_id"
    t.boolean "affiliate_links", default: true
    t.boolean "approved", default: false
    t.boolean "submitted", default: false
    t.index ["image_id"], name: "index_blogs_on_image_id"
    t.index ["pdf_id"], name: "index_blogs_on_pdf_id"
    t.index ["pin_image_id"], name: "index_blogs_on_pin_image_id"
    t.index ["slug"], name: "index_blogs_on_slug", unique: true
    t.index ["subcategory_id"], name: "index_blogs_on_subcategory_id"
    t.index ["user_id"], name: "index_blogs_on_user_id"
  end

It seems that this might be an SQLite thing, because this post and this one seem to be having a similar problem. However, neither post involves an actual answer. Has anyone successfully gotten rid of this?

Answer 1

UPDATE:

A new column default can be added via Rails without having to use the database. In the Blog model, we can use ActiveRecord::Attributes::ClassMethods::attribute to redefine the default value for freebie_type:

attribute :freebie_type, :string, default: 'None'

This will change the default at the business logic level. Therefore, it is dependent on using ActiveRecord to be recognized. Manipulation of the database via SQL will still use the old default. To update the default in all cases see the original answer below.

ORIGINAL ANSWER:

Unfortunately, ALTER COLUMN is only minimally supported by SQLite. The work around it to create a new table, copy the information to it, drop the old table, and finally rename the new table. This is what Rails is attempting to do, but without first disabling the foreign key constraints. The foreign key relations to user_id, image_id, and pdf_id are preventing the table deletion.

You will need to do the update manually, either with SQL (preferred) or ActiveRecord::Base.connection. You can see the process here under 'Modify column in table'. You can find all the options available for columns in the SQLite Create Table Documentation.

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE table1 RENAME TO _table1_old;

CREATE TABLE table1 (
( column1 datatype [ NULL | NOT NULL ] DEFAULT (<MY_VALUE>),
  column2 datatype [ NULL | NOT NULL ] DEFAULT (<MY_VALUE>),
  ...
);

INSERT INTO table1 (column1, column2, ... column_n)
  SELECT column1, column2, ... column_n
  FROM _table1_old;

COMMIT;

PRAGMA foreign_keys=on;

Be certain that you have all the columns set up the way you want as you will not be able to fix it after the table is created! Going forward I would highly recommend setting up either a PostgreSQL or MySQL2 database. They are much more powerful and will be much easier to modify and maintain.

Answer 2

You can add an initializer to monkey patch the sqlite adapter to make it work with rails 5, just make sure you have sqlite >= 3.8, with this code:

blog/config/initializers/sqlite3_disable_referential_to_rails_5.rb

Content:

require 'active_record/connection_adapters/sqlite3_adapter'

module ActiveRecord
  module ConnectionAdapters
    class SQLite3Adapter < AbstractAdapter

      # REFERENTIAL INTEGRITY ====================================

      def disable_referential_integrity # :nodoc:
        old_foreign_keys = query_value("PRAGMA foreign_keys")
        old_defer_foreign_keys = query_value("PRAGMA defer_foreign_keys")

        begin
          execute("PRAGMA defer_foreign_keys = ON")
          execute("PRAGMA foreign_keys = OFF")
          yield
        ensure
          execute("PRAGMA defer_foreign_keys = #{old_defer_foreign_keys}")
          execute("PRAGMA foreign_keys = #{old_foreign_keys}")
        end
      end

      def insert_fixtures_set(fixture_set, tables_to_delete = [])
        disable_referential_integrity do
          transaction(requires_new: true) do
            tables_to_delete.each {|table| delete "DELETE FROM #{quote_table_name(table)}", "Fixture Delete"}

            fixture_set.each do |table_name, rows|
              rows.each {|row| insert_fixture(row, table_name)}
            end
          end
        end
      end

      private

      def alter_table(table_name, options = {})
        altered_table_name = "a#{table_name}"
        caller = lambda {|definition| yield definition if block_given?}

        transaction do
          disable_referential_integrity do
            move_table(table_name, altered_table_name,
                       options.merge(temporary: true))
            move_table(altered_table_name, table_name, &caller)
          end
        end
      end
    end
  end
end

Here is the gist: https://gist.github.com/javier-menendez/3cfa71452229f8125865a3247fa03d51