rails: New reference to old table fails on foreign key (MySQL bigint(20) v. int(11))

The line t.references :role, foreign_key: true is incorrect - it doesn’t correctly reflect the desired behavior. I believe it would work if it specified the type of the reference explicitly:

  t.references :role, foreign_key: true, type: :integer

The default for type was changed in #26266 but the documentation here didn’t get updated.

@dankozlowski there are two different “fixes”. One involves claiming that the migrations are [5.0], but you’re already using Rails 5.0.4 so that can’t be it. The other fix is to specify the :type of the foreign key as @al2o3cr indicated (and I verified that it worked for me having a new 5.1 join table include a reference to a pre-5.1 table):

t.references :role, foreign_key: true, type: :integer

In my case, the targeted table was actually created in a Rails 4.x system.

How are you encountering this issue in Rails 5.0.4?

I had the same issue, and for any future visitors, it turns out I hadn’t set the RAILS_ENV; I have no idea how this solves the problem but it worked for me. None of the other solutions worked.

bin/rails db:environment:set RAILS_ENV=development

Then a good old reset:

bin/rails db:reset

Everything worked like a charm then.

I’m using Rails 5.1.6 and Ruby 2.5.0 on macOS High Sierra.

I’m having this issue as well, and @rab 's fix doesn’t work for me. I’m using Rails 5.0.4.

I’m running into this issue after my migrations started being numbered at 5.1 and it’s really annoying. I can’t have foreign keys right now in my migrations.

Getting same error now

rails (7.0.4.3) ruby (3.2.2) mysql (2-0.5.4)

class CreateJQISContractorSnapshots < ActiveRecord::Migration[7.0]
  def change
    create_table :jqis_contractor_snapshots do |t|
      t.belongs_to :contractor, null: false, foreign_key: true, index: true, type: :bigint
    
      t.timestamps
    end
  end

Column `contractor_id` on table `jqis_contractor_snapshots` does not match column `id` on `contractors`, which has type `bigint(20)`. To resolve this issue, change the type of the `contractor_id` column on `jqis_contractor_snapshots` to be :bigint. (For example `t.bigint :contractor_id`).
Original message: Mysql2::Error: Cannot add foreign key constraint
/src/vendor/bundle/ruby/3.2.0/gems/mysql2-0.5.4/lib/mysql2/client.rb:148:in `_query'

I think the error handling for this has had some weird knock-on effects. We’re getting this error in our migrations, and while we haven’t found a resolution, we’re getting instructions to change to :integer even though that’s clearly not the solution.

Further details:

Existing Schema

  create_table "accounts", id: :string, limit: 36, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8", force: :cascade do |t|
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["id"], name: "index_accounts_on_id"
  end

Migration

class CreateComments < ActiveRecord::Migration[5.2]
  def change
    create_table :comments do |t|
      t.text :content
      t.string :author_id, type: :string, limit: 36
      t.timestamps
    end

    add_foreign_key :comments, :accounts, column: :author_id
  end
end

Error Message

Column `author_id` on table `comments` has a type of `varchar(36)`.
This does not match column `id` on `accounts`, which has type `varchar(36)`.
To resolve this issue, change the type of the `author_id` column on `comments` to be :integer. (For example `t.integer author_id`).

Original message: Mysql2::Error: Cannot add foreign key constraint: ALTER TABLE `comments` ADD CONSTRAINT `fk_rails_f44b1e3c8a`
FOREIGN KEY (`author_id`)
  REFERENCES `accounts` (`id`)

For any foreign key that is a reference to an old table, add type: :integer to fix the type to be explicitly integer – int(11), that is – rather than the default :bigint – aka, bigint(20).