Laravel 外部キー制約エラー General error: 3780 Referencing column #Laravel

Laravel 外部キー制約を張る際に起こるエラーの対処法です。

環境

PHP 7.3.8
Laravel 6.7
MySQL 8.0.17

外部キー制約を書いたマイグレーションを実行するとエラー

エラーメッセージ: General error: 3780 Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'tasks_user_id_foreign' are incompatible.

$ php artisan migrate
Dropped all tables successfully.
Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table (0.04 seconds)
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table (0.02 seconds)
Migrating: 2019_08_19_000000_create_failed_jobs_table
Migrated:  2019_08_19_000000_create_failed_jobs_table (0.01 seconds)
Migrating: 2019_12_28_000000_create_tasks_table

   Illuminate\Database\QueryException  : SQLSTATE[HY000]: General error: 3780 Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'tasks_user_id_foreign' are incompatible. (SQL: alter table `tasks` add constraint `tasks_user_id_foreign` foreign key (`user_id`) references `users` (`id`))

  at /work/vendor/laravel/framework/src/Illuminate/Database/Connection.php:669
    665|         // If an exception occurs when attempting to run a query, we'll format the error
    666|         // message to include the bindings with SQL, which will make this exception a
    667|         // lot more helpful to the developer instead of just the database's errors.
    668|         catch (Exception $e) {
  > 669|             throw new QueryException(
    670|                 $query, $this->prepareBindings($bindings), $e
    671|             );
    672|         }
    673|

  Exception trace:

  1   PDOException::("SQLSTATE[HY000]: General error: 3780 Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'tasks_user_id_foreign' are incompatible.")
      /work/vendor/laravel/framework/src/Illuminate/Database/Connection.php:463

  2   PDOStatement::execute()
      /work/vendor/laravel/framework/src/Illuminate/Database/Connection.php:463

  Please use the argument -v to see more details.

users テーブルのマイグレーション

database/migrations/2014_10_12_000000_create_users_table.php

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users');
    }
}

tasks テーブルのマイグレーション

database/migrations/2019_12_28_000000_create_tasks_table.php

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateTasksTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('tasks', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->bigInteger('user_id');
            $table->string('content');
            $table->timestamps();

            // 外部キー制約を張る
            $table->foreign('user_id')->references('id')->on('users');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('tasks');
    }
}

users テーブル構成

mysql> desc users;
+-------------------+---------------------+------+-----+---------+----------------+
| Field             | Type                | Null | Key | Default | Extra          |
+-------------------+---------------------+------+-----+---------+----------------+
| id                | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| name              | varchar(255)        | NO   |     | NULL    |                |
| email             | varchar(255)        | NO   | UNI | NULL    |                |
| email_verified_at | timestamp           | YES  |     | NULL    |                |
| password          | varchar(255)        | NO   |     | NULL    |                |
| remember_token    | varchar(100)        | YES  |     | NULL    |                |
| created_at        | timestamp           | YES  |     | NULL    |                |
| updated_at        | timestamp           | YES  |     | NULL    |                |
+-------------------+---------------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)

補足: unsigned

unsigned と符号なしの指定すると負の値を使えなくなります。

符号付きの範囲は -9223372036854775808 から 9223372036854775807 です。
符号なしの範囲は 0 から 18446744073709551615 となります。

修正方法

$table->bigIncrements('id'); と定義すると users.id は bigint(20) unsigned の型で定義されます。

database/migrations/2019_12_28_000000_create_tasks_table.php

            // $table->bigInteger('user_id');
            $table->unsignedBigInteger('user_id');

下記の指定でもokです。

database/migrations/2019_12_28_000000_create_tasks_table.php

            $table->bigInteger('user_id')->unsigned();

テーブルを作り直す

$ php artisan migrate
Migrating: 2019_12_28_000000_create_tasks_table

   Illuminate\Database\QueryException  : SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'tasks' already exists (SQL: create table `tasks` (`id` bigint unsigned not null auto_increment primary key, `user_id` bigint unsigned not null, `content` varchar(255) not null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci')

  at /work/vendor/laravel/framework/src/Illuminate/Database/Connection.php:669
    665|         // If an exception occurs when attempting to run a query, we'll format the error
    666|         // message to include the bindings with SQL, which will make this exception a
    667|         // lot more helpful to the developer instead of just the database's errors.
    668|         catch (Exception $e) {
  > 669|             throw new QueryException(
    670|                 $query, $this->prepareBindings($bindings), $e
    671|             );
    672|         }
    673|

  Exception trace:

  1   PDOException::("SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'tasks' already exists")
      /work/vendor/laravel/framework/src/Illuminate/Database/Connection.php:463

  2   PDOStatement::execute()
      /work/vendor/laravel/framework/src/Illuminate/Database/Connection.php:463

  Please use the argument -v to see more details.

php artisan migrate だと tasks テーブルの作成までは成功してしまってるので、既に tasks テーブルは作成されているというエラーが発生してしまいます。
開発環境などテーブルデータが消えても問題ない場合は migrate:fresh コマンドでテーブルを丸ごと作り直した方がお手軽です！

$ php artisan migrate:fresh
Dropped all tables successfully.
Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table (0.03 seconds)
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table (0.02 seconds)
Migrating: 2019_08_19_000000_create_failed_jobs_table
Migrated:  2019_08_19_000000_create_failed_jobs_table (0.01 seconds)
Migrating: 2019_12_28_000000_create_tasks_table
Migrated:  2019_12_28_000000_create_tasks_table (0.04 seconds)

補足: Laravel 5.7以下

Laravel 5.7以下では $table->increments('id'); がデフォルトとなってます。
なので、古い記事や書籍によってそのまま進めるとこういったエラーが発生することが多いです。