Laravel10+MySQL8添加user_id外键遇重复约束错误求助

问题原因 #

MySQL要求数据库内的外键约束名称必须全局唯一。你当前的迁移代码中，foreignUuid('user_id')->constrained()未显式指定约束名，Laravel自动生成约束名时出现重复值1，触发了Duplicate foreign key constraint name错误。

解决方法 #

手动为每个表的外键约束指定唯一名称，约束名建议遵循表名_字段名_foreign的规范，确保不会重复。

修改后的Campaigns迁移代码 #

<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        if (! Schema::hasTable('campaigns')) {
            Schema::create('campaigns', function (Blueprint $table) {
                $table->uuid('id')->primary();
                $table->foreignUuid('user_id')
                    ->constrained()
                    ->name('campaigns_user_id_foreign')
                    ->index();
                $table->string('name');
                $table->tinyInteger('priority')->default(100);
                $table->dateTime('send_at');
                $table->timestamps();
            });
        }
    }

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

修改后的MarketingLists迁移代码 #

<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        if (! Schema::hasTable('marketing_lists')) {
            Schema::create('marketing_lists', function (Blueprint $table) {
                $table->uuid('id')->primary();
                $table->foreignUuid('user_id')
                    ->constrained()
                    ->name('marketing_lists_user_id_foreign')
                    ->index();
                $table->string('name');
                $table->timestamps();
            });
        }
    }

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

后续操作 #

如果已经执行过迁移导致数据库中存在错误约束：

• 优先执行回滚命令：php artisan migrate:rollback，再重新执行迁移：php artisan migrate
• 若回滚失败，可直接在MySQL中删除错误约束：ALTER TABLE marketing_lists DROP FOREIGN KEY 1;，再重新运行迁移命令。

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