I want to add a reference type column later

Target of this article

・ Those who want to add a reference type column to the table later but are struggling ・ Those who have been learning programming for several months

** ⚠️ This is a record of what I tried halfway through, so if you just want to see how to do it, please see the heading "The correct answer is as follows" **

Thing you want to do

-I want to add a reference type column that I forgot to add to the table

background

I am currently creating an original app. It is an application that consists of only two users table and records table, and has a relationship of users: records = 1: many.

In a one-to-many relationship, it is common for many to have a reference-type column with a foreign key constraint (foreign_key: true).

Therefore, in an application like the above, after creating some user functions, I should have created a table containing the other references type column, but I completely forgot the existence of the references type column and put it in the root path. When creating the top page, for some reason I also created the records table without the foreign key column.

** ⚠️ Naturally, if you wrote the references type column in the migration file at this stage ↑, an error will occur because the users table to be referenced does not exist. ** **

Then, when I tried to display the posts (information in the records table) associated with the user, I finally realized that I had not added a reference type column, and I had a hard time.

hypothesis

From what I've learned so far, there are two possible ways to add columns later.

Method ① Edit the migration file of the table to which you want to add columns after turning down the existing migration whose status is up.

Method ② Execute the command rails g migration Add column name To table name and set add_column: table name,: column name,: data type, option in the change method of the newly created migration file. Describe.

Personally, I felt that ① was easier to understand when adding or changing columns, so I tried ① first.

Practice "(1) Down the existing migration whose status is up, and then edit the migration file of the table to which you want to add columns"

①-1. Check the contents of the current migration file with rails db: migrate: status

 Status   Migration ID    Migration Name
--------------------------------------------------
   up     20201206094413  Create records
   up     20201206115636  Devise create users

①-2. Confirm that it is down after doing rails db: rollback

 Status   Migration ID    Migration Name
--------------------------------------------------
  down    20201206094413  Create records
  down    20201206115636  Devise create users

①-3. I want to add a user_id column of reference type 20201206094413 20201206094413 Create records Because I am a person, I edited the corresponding file as follows.

20201206094413_create_records.rb

class CreateRecords < ActiveRecord::Migration[6.0]
  def change
    create_table :records do |t|
      t.integer :time, null: false
      t.string :skip, null: false
      t.string :to_do
      t.references :user, foreign_key: true
      t.timestamps
    end
  end
end

①-4. After executing rails db: migrate, the output is as follows.

== 20201206094413 CreateRecords: migrating ====================================
-- create_table(:records)
rails aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: Table 'app name_development.users' doesn't exist
/Users/username/projects/app name/db/migrate/20201206094413_create_records.rb:3:in `change'
/Users/username/projects/app name/bin/rails:9:in `<top (required)>'
/Users/username/projects/app name/bin/spring:15:in `<top (required)>'
bin/rails:3:in `load'
bin/rails:3:in `<main>'

Caused by:
ActiveRecord::StatementInvalid: Mysql2::Error: Table 'app name_development.users' doesn't exist
/Users/username/projects/app name/db/migrate/20201206094413_create_records.rb:3:in `change'
/Users/username/projects/app name/bin/rails:9:in `<top (required)>'
/Users/username/projects/app name/bin/spring:15:in `<top (required)>'
bin/rails:3:in `load'
bin/rails:3:in `<main>'

Caused by:
Mysql2::Error: Table 'app name_development.users' doesn't exist
/Users/username/projects/app name/db/migrate/20201206094413_create_records.rb:3:in `change'
/Users/username/projects/app name/bin/rails:9:in `<top (required)>'
/Users/username/projects/app name/bin/spring:15:in `<top (required)>'
bin/rails:3:in `load'
bin/rails:3:in `<main>'

Caused by:
Mysql2::Error: Cannot add foreign key constraint
/Users/username/projects/app name/db/migrate/20201206094413_create_records.rb:3:in `change'
/Users/username/projects/app name/bin/rails:9:in `<top (required)>'
/Users/username/projects/app name/bin/spring:15:in `<top (required)>'
bin/rails:3:in `load'
bin/rails:3:in `<main>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

I couldn't create a table. The following two points were noted in the error message.

Mysql2::Error: Table 'app name_development.users' doesn't exist

Mysql2::Error: Cannot add foreign key constraint

I thought that "the foreign key constraint cannot be added because the users table does not exist", but at first the migration file that creates the users table is only down and not deleted, so at first I wasn't sure.

In the end, I didn't know the exact answer, but I thought it was judged that there was no table that could be referenced because the order in which the migration files were read was that the one that created the records table was earlier than the one that created the users table. ** (This is because I created the migration file for creating the records table first) **

The reason is that there is no error message related to reading the users table in the above error message, and it is natural to think that there is a flow that the operation stopped due to the failure to read the records table.

In other words, I thought that it might work if only the users table could be read first, and when I investigated how to execute only the specified migration file, I found out.

rake db: migrate VERSION = migration file ID

It seems that it is possible by executing, so I entered the ID of the migration file in the users table and executed it, but the error message in ①-4 I got exactly the same thing as.

At this point, we decided that the hypothetical method (1) was impossible, and removed the references type from the migration file. I executed rails db: migrate to return to the original table state, and started method (2).

Execute the command "②rails g migration Add column name To table name ``, and in the change method of the newly created migration file, `` add_column: table name,: column name,: data type, option Practice "Write`. "

②-1. Execute rails g migration AddUserToRecords and edit the generated migration file as follows.

class AddUserToRecord < ActiveRecord::Migration[6.0]
  def change
    add_reference :records, :user, null: false, foreign_key: true
  end
end

After all, neither of my hypotheses was able to add a column of type references.

The correct answer is as follows

① rails g migration file name (camel or snake case) model name: create a migration file with references (In my case, rails g migration Add_User_id_To_Record user: references)

(2) Edit the generated migration file as follows

20201212172855_add_user_id_to_record.rb

class AddUserIdToRecord < ActiveRecord::Migration[6.0]
  def change
    add_reference :records, :user, null: false, foreign_key: true
  end
end

③ Execute rails db: migrate and the output is as follows

== 20201212172855 AddUserIdToRecord: migrating ================================
-- add_reference(:records, :user, {:null=>false, :foreign_key=>true})
   -> 0.2707s
== 20201212172855 AddUserIdToRecord: migrated (0.2709s) =======================

④ Check with Sequel Pro

Reference article

it-swarm-ja.tech "How to Roll Back a Specific Migration" https://www.it-swarm-ja.tech/ja/ruby-on-rails/%E7%89%B9%E5%AE%9A%E3%81%AE%E7%A7%BB%E8%A1%8C%E3%82%92%E3%83%AD%E3%83%BC%E3%83%AB%E3%83%90%E3%83%83%E3%82%AF%E3%81%99%E3%82%8B%E6%96%B9%E6%B3%95/970919017/ (Viewed on December 12, 2020)

A memorandum of the Web engineer in that area "If you try to add a references column later, you cannot migrate db: migrate" https://kossy-web-engineer.hatenablog.com/entry/2018/09/03/022121 (Viewed on December 12, 2020)

Qiita (@ ryouya3948) "[Rails] Foreign key constraints and reference types" https://qiita.com/ryouya3948/items/7417c3acabb4d5e1657b (Viewed on December 12, 2020)