Laravel 8执行migrate:fresh时迁移失败，请求排查外键约束问题

问题描述 #

学习《Laravel 8 From Scratch》第54集时，执行php artisan migrate:fresh --seed触发外键约束错误，无法创建posts表，提示外键引用的categories表不存在。

错误日志：

lance@lance-vivobook:~/laravel-blog$ php artisan migrate:fresh

  Dropping all tables ............................................. 38ms DONE

   INFO  Preparing database.  

  Creating migration table ......................................... 8ms DONE

   INFO  Running migrations.  

  2014_10_12_000000_create_users_table ............................ 32ms DONE
  2014_10_12_100000_create_password_reset_tokens_table ............ 19ms DONE
  2019_08_19_000000_create_failed_jobs_table ...................... 17ms DONE
  2019_12_14_000001_create_personal_access_tokens_table ........... 28ms DONE
  2023_07_14_080115_create_posts_table ............................ 40ms FAIL

   Illuminate\Database\QueryException 

  SQLSTATE[HY000]: General error: 1005 Can't create table `blog`.`posts` (errno: 150 "Foreign key constraint is incorrectly formed") (Connection: mysql, SQL: alter table `posts` add constraint `posts_category_id_foreign` foreign key (`category_id`) references `categories` (`id`) on delete cascade)

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:795
    791▕         // If an exception occurs when attempting to run a query, we'll format the error
    792▕         // message to include the bindings with SQL, which will make this exception a
    793▕         // lot more helpful to the developer instead of just the database's errors.
    794▕         catch (Exception $e) {
  ➜ 795▕             throw new QueryException(
    796▕                 $this->getName(), $query, $this->prepareBindings($bindings), $e
    797▕             );
    798▕         }
    799▕     }

      +9 vendor frames 

  10  database/migrations/2023_07_14_080115_create_posts_table.php:24
      Illuminate\Support\Facades\Facade::__callStatic()
      +35 vendor frames 

  46  artisan:37
      Illuminate\Foundation\Console\Kernel::handle()

核心原因 #

Laravel迁移按文件名的时间戳顺序执行，create_categories_table.php的时间戳晚于create_posts_table.php，导致创建posts表时，categories表还未被创建，数据库无法识别外键关联的目标表，因此抛出约束错误。

解决方案 #

调整迁移文件顺序 #

修改create_categories_table.php的文件名时间戳，确保它早于create_posts_table.php的时间戳。例如：

• 原posts迁移文件名：2023_07_14_080115_create_posts_table.php
• 修改后categories迁移文件名：2023_07_14_080000_create_categories_table.php

修改后重新执行迁移命令：

php artisan migrate:fresh --seed

额外验证 #

确保categories表的id字段类型与posts表的category_id字段类型一致（两者均为unsignedBigInteger，代码中已通过$table->id()和$table->foreignId()实现，无需额外修改）。

相关代码参考 #

create_posts_table.php #

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->constrained()->cascadeOnDelete();
            $table->foreignId('category_id')->constrained()->cascadeOnDelete();
            $table->string('slug')->unique();
            $table->string('title');
            $table->text('excerpt');
            $table->text('body');
            $table->timestamps();
            $table->timestamp('published_at')->nullable();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('posts');
    }
};

create_categories_table.php #

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('categories', function (Blueprint $table) {
            $table->id();
            $table->string('name')->unique();
            $table->string('slug')->unique();
            $table->timestamps();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('categories');
    }
};

内容的提问来源于stack exchange，提问作者lance2k