I recently migrated a Rails 4 application to Rails 7. On the whole it wasn’t too bad but today I was trying to add a child table to the data schema – a simple create_table… When I attempted to “db:migrate” I got an error:

$ rails db:migrate
== 20220520112518 CreateArticleTexts: migrating ===============================
-- create_table(:article_texts)
rails aborted!
StandardError: An error has occurred, all later migrations canceled:

Column `article_id` on table `article_texts` does not match column `id` on `articles`, which has type `int(11)`. To resolve this issue, change the type of the `article_id` column on `article_texts` to be :integer. (For example `t.integer :article_id`).
Original message: Mysql2::Error: Cannot add foreign key constraint
/home/myproject/db/migrate/20220520112518_create_article_texts.rb:3:in `change'

Caused by:
ActiveRecord::MismatchedForeignKey: Column `article_id` on table `article_texts` does not match column `id` on `articles`, which has type `int(11)`. To resolve this issue, change the type of the `article_id` column on `article_texts` to be :integer. (For example `t.integer :article_id`).
Original message: Mysql2::Error: Cannot add foreign key constraint
/home/myproject/db/migrate/20220520112518_create_article_texts.rb:3:in `change'

Caused by:
Mysql2::Error: Cannot add foreign key constraint
/home/myproject/db/migrate/20220520112518_create_article_texts.rb:3:in `change'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

My old schema (from Rails 4 remember) used :int for the primary key data type. Rails 7 (and Rails 6 – I tested it) uses :bigint for the primary key data type.

In order to create a new child table I first need to upgrade the primary key on the parent table. After much trial and error the migration below is what I came up with for MySQL. In this example I also had 2 other legacy child tables which needed updating also so that foreign keys and primary keys had matching data types:

class ArticleUpdateId < ActiveRecord::Migration[7.0]
  #
  # For Rails 6+ we need legacy tables to have primary keys updated from :int to :bigint
  # before we can add any new child tables. The migrations below are for MySQL.
  #

  def up
    # drop primary key index and foreign key constraints otherwise we cannot make changes
    execute('ALTER TABLE article_filters DROP FOREIGN KEY fk_rails_303034c0ef')
    execute('ALTER TABLE article_keywords DROP FOREIGN KEY fk_rails_251b14e14a')
    execute('ALTER TABLE articles CHANGE id id int') # first remove AUTO_INCREMENT
    execute('ALTER TABLE articles DROP PRIMARY KEY')

    # old article_id child fields did not use bigint
    execute('ALTER TABLE article_filters CHANGE article_id article_id bigint NOT NULL')
    execute('ALTER TABLE article_keywords CHANGE article_id article_id bigint NOT NULL')

    # old id fields did not use bigint
    execute('ALTER TABLE articles CHANGE id id bigint PRIMARY KEY AUTO_INCREMENT')

    # recreate foreign key constraints
    execute 'ALTER TABLE article_filters ADD CONSTRAINT fk_rails_303034c0ef FOREIGN KEY (article_id) REFERENCES articles (id)'
    execute 'ALTER TABLE article_keywords ADD CONSTRAINT fk_rails_251b14e14a FOREIGN KEY (article_id) REFERENCES articles (id)'
  end

  def down
    # drop primary key index and foreign key constraints otherwise we cannot make changes
    execute('ALTER TABLE article_filters DROP FOREIGN KEY fk_rails_303034c0ef')
    execute('ALTER TABLE article_keywords DROP FOREIGN KEY fk_rails_251b14e14a')
    execute('ALTER TABLE articles CHANGE id id bigint') # first remove AUTO_INCREMENT
    execute('ALTER TABLE articles DROP PRIMARY KEY')

    # back to int on child table references
    execute('ALTER TABLE article_filters CHANGE article_id article_id int NOT NULL')
    execute('ALTER TABLE article_keywords CHANGE article_id article_id int NOT NULL')

    # back to int on parent table
    execute('ALTER TABLE articles CHANGE id id int PRIMARY KEY AUTO_INCREMENT')

    # recreate foreign key constraints
    execute 'ALTER TABLE article_filters ADD CONSTRAINT fk_rails_303034c0ef FOREIGN KEY (article_id) REFERENCES articles (id)'
    execute 'ALTER TABLE article_keywords ADD CONSTRAINT fk_rails_251b14e14a FOREIGN KEY (article_id) REFERENCES articles (id)'
  end
end

This seems to work well so far. Neither ID nor auto_increment values were affected by the migration and my parent id and child article_id columns are now bigint.