マイグレーション実行するがエラーが発生!(外部キー制約について)


前提

・外部キー制約設定時のエラーについてです!
・自分は1時間ほど、かかてってしまってので共有させていてだきます。
指摘やアドバイスなどいただけたら嬉しいです。

環境

Docker version 20.10.5,
PHP 7.3.8
Laravel 6.0

エラー箇所

マイグレーションを実行すると下記のエラーが出ました。

% docker-compose exec app 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.14 seconds)
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table (0.13 seconds)
Migrating: 2019_08_19_000000_create_failed_jobs_table
Migrated:  2019_08_19_000000_create_failed_jobs_table (0.1 seconds)
Migrating: 2021_05_24_195727_create_articles_table

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

  at /var/www/html/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 'articles_user_id_foreign' are incompatible.")
      /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:463

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

  Please use the argument -v to see more details.

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

<?php

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

class CreateArticlesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('articles', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('title');
            $table->text('body');
            $table->bigInteger('user_id');
            // 外部キー制約
            $table->foreign('user_id')->references('id')->on('users');
            $table->timestamps();
        });
    }

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

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

<?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')->unique();
            $table->string('email')->unique();
            // nullを許容
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password')->nullable();
            $table->rememberToken();
            $table->timestamps();
        });
    }

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

解決方法

Articlesテーブル

外部キー制約にunsigned()をつけてあげます!
理由としましては、裏でunsigned(符号なし)が付与されます!
採番項目なので正の値しか登録できないわけなので、user_idに制約をつけていない。
つまり、形式が違いますよ!ってことです。。

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

後は、テーブルが消えても問題ない場合はfreshを実行してください!
これで完成です!!

% docker-compose exec app 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.17 seconds)
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table (0.08 seconds)
Migrating: 2019_08_19_000000_create_failed_jobs_table
Migrated:  2019_08_19_000000_create_failed_jobs_table (0.05 seconds)
Migrating: 2021_05_24_195727_create_articles_table
Migrated:  2021_05_24_195727_create_articles_table (0.32 seconds)