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