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.