【エラーで困っています】Cannot add foreign key constraint

前提・実現したいこと

Rails、Docker、MySQLを用いて開発をしています。
バージョンは以下の通りです。

開発環境
Ruby vresion2.7.2
Rails version6.0.3.4
MySQL version 5.7

現在外部キーを用いて新たなテーブルを作成しようとしているのですが、
docker-compose run web rails db:migrate
をすると、エラーが出てしまいます。
どうかお力を貸していただけますでしょうか？

発生している問題・エラーメッセージ

docker-compose run web rails db:migrateを打ったら以下のようなエラーが出てきます。

== 20201223141709 CreateBookcases: migrating ==================================
-- create_table(:bookcases)
rails aborted!
StandardError: An error has occurred, all later migrations canceled:

Column `user_id` on table `bookcases` does not match column `id` on `users`, which has type `bigint(20)`. To resolve this issue, change the type of the `user_id` column on `bookcases` to be :bigint. (For example `t.bigint :user_id`).
Original message: Mysql2::Error: Cannot add foreign key constraint
/usr/local/bundle/gems/mysql2-0.5.3/lib/mysql2/client.rb:131:in `_query'
/usr/local/bundle/gems/mysql2-0.5.3/lib/mysql2/client.rb:131:in `block in query'
/usr/local/bundle/gems/mysql2-0.5.3/lib/mysql2/client.rb:130:in `handle_interrupt'
/usr/local/bundle/gems/mysql2-0.5.3/lib/mysql2/client.rb:130:in `query'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:201:in `block (2 levels) in execute'
/usr/local/bundle/gems/activesupport-6.0.3.4/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
/usr/local/bundle/gems/activesupport-6.0.3.4/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
/usr/local/bundle/gems/activesupport-6.0.3.4/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:200:in `block in execute'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/connection_adapters/abstract_adapter.rb:722:in `block (2 levels) in log'
/usr/local/bundle/gems/activesupport-6.0.3.4/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 levels) in synchronize'
/usr/local/bundle/gems/activesupport-6.0.3.4/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
/usr/local/bundle/gems/activesupport-6.0.3.4/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
/usr/local/bundle/gems/activesupport-6.0.3.4/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
/usr/local/bundle/gems/activesupport-6.0.3.4/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/connection_adapters/abstract_adapter.rb:721:in `block in log'
/usr/local/bundle/gems/activesupport-6.0.3.4/lib/active_support/notifications/instrumenter.rb:24:in `instrument'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/connection_adapters/abstract_adapter.rb:712:in `log'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:199:in `execute'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/connection_adapters/mysql/database_statements.rb:41:in `execute'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/connection_adapters/abstract/schema_statements.rb:315:in `create_table'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/connection_adapters/mysql/schema_statements.rb:81:in `create_table'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:890:in `block in method_missing'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:858:in `block in say_with_time'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:858:in `say_with_time'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:879:in `method_missing'
/share-read/db/migrate/20201223141709_create_bookcases.rb:3:in `change'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:828:in `exec_migration'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:812:in `block (2 levels) in migrate'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:811:in `block in migrate'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:471:in `with_connection'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:810:in `migrate'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:1002:in `migrate'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:1310:in `block in execute_migration_in_transaction'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:1363:in `ddl_transaction'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:1309:in `execute_migration_in_transaction'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:1281:in `block in migrate_without_lock'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:1280:in `each'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:1280:in `migrate_without_lock'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:1229:in `block in migrate'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:1382:in `with_advisory_lock'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:1229:in `migrate'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:1061:in `up'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:1036:in `migrate'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/tasks/database_tasks.rb:238:in `migrate'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/railties/databases.rake:86:in `block (3 levels) in <main>'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/railties/databases.rake:84:in `each'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/railties/databases.rake:84:in `block (2 levels) in <main>'
/usr/local/bundle/gems/railties-6.0.3.4/lib/rails/commands/rake/rake_command.rb:23:in `block in perform'
/usr/local/bundle/gems/railties-6.0.3.4/lib/rails/commands/rake/rake_command.rb:20:in `perform'
/usr/local/bundle/gems/railties-6.0.3.4/lib/rails/command.rb:48:in `invoke'
/usr/local/bundle/gems/railties-6.0.3.4/lib/rails/commands.rb:18:in `<main>'
/usr/local/bundle/gems/bootsnap-1.5.1/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:23:in `require'
/usr/local/bundle/gems/bootsnap-1.5.1/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:23:in `block in require_with_bootsnap_lfi'
/usr/local/bundle/gems/bootsnap-1.5.1/lib/bootsnap/load_path_cache/loaded_features_index.rb:92:in `register'
/usr/local/bundle/gems/bootsnap-1.5.1/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:22:in `require_with_bootsnap_lfi'
/usr/local/bundle/gems/bootsnap-1.5.1/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:31:in `require'
/usr/local/bundle/gems/activesupport-6.0.3.4/lib/active_support/dependencies.rb:324:in `block in require'
/usr/local/bundle/gems/activesupport-6.0.3.4/lib/active_support/dependencies.rb:291:in `load_dependency'
/usr/local/bundle/gems/activesupport-6.0.3.4/lib/active_support/dependencies.rb:324:in `require'
bin/rails:4:in `<main>'

該当のソースコード

マイグレーションファイル

class CreateBookcases < ActiveRecord::Migration[6.0]
  def change
    create_table :bookcases do |t|
      t.integer :read
      t.references :user, null: false, foreign_key: true
      t.references :book, null: false, foreign_key: true

      t.timestamps

      t.index [:user_id, :book_id], unique: true
    end
  end
end

schema.rb(該当のモデルのみ記載)

  create_table "books", primary_key: "isbn", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", force: :cascade do |t|
    t.string "title"
    t.string "author"
    t.string "url"
    t.string "image_url"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.string "book_genre_id"
  end

  create_table "users", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", force: :cascade do |t|
    t.string "name"
    t.string "email"
    t.string "password_digest"
    t.string "user_image"
    t.integer "gender"
    t.date "birthday"
    t.text "introduction"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

試したこと

以下の3つのことを試しましたが、うまく行きませんでした。
①dockerの再起動
docker-compose downとdocker-compose up -dを試しましたが、うまく行きませんでした。

②マイグレーションファイル内の外部キーのtypeを指定
type: bigintを指定しましたが、うまく行きませんでした。

 t.references :user, type: bigint, null: false, foreign_key: true
 t.references :book, type: bigint, null: false, foreign_key: true

②データベースのリセット
docker-compose run web rails db:resetをしました、エラーが出てしまいうまくできませんでした。

あくまで、外部キーとして利用したいため、t.referencesの記載は変えておりません。

何かわかることがございましたら、コメントをいただけると幸いです。
よろしくお願いいたします。

補足情報

PCはMacを使用しております。

m.ts10806

2020/12/23 22:40

先に外部キー指定先のテーブルが作られてないとかそんな気もします。作成順も大事です。

u-jr

2020/12/24 07:19

ご回答ありがとうございます。 rails db:migrate:statusをしたところ、以下のような表示がでたため、指定先（use,book)のテーブルを作られていると考えています。 up 20201207135139 Create users up 20201218052640 Create books

行動規範の内容に同意します

回答1件

自己解決

以下のことを追加で試して解決しました（質問した後何回か上記のエラーが出たため、色々試しています）。
同じように困った方がいらっしゃったら参考にしてみてください。
①docker-compose run web rails dbで直接mysqlを操作して該当テーブルを削除
②mysql/volumes/*******_development(*****内は自分のアプリ名）の該当するファイル（ibdやrfmファイル）を削除
③データベースファイルを削除し、docker-compose run web rails:createとdocker-compose run web rails db:migrateをして最初からやり直す

投稿2020/12/25 14:33

u-jr

総合スコア0