Help us understand the problem. What is going on with this article?

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

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.idbigint(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'); がデフォルトとなってます。
なので、古い記事や書籍によってそのまま進めるとこういったエラーが発生することが多いです。

ucan-lab
Backend Developer at ROLO. I love PHP and I'm focusing on Laravel, Docker, GraphQL.
https://u-can.pro
miraito-inc
システムデザインを中心に置いた開発により高品質で使いやすいシステムを提供いたします。業務システム構築、アプリ開発、コンサルティングまで幅広く手がけています。
https://miraito-inc.co.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした