Laravel迁移删除外键约束遇SQL 1091错误如何解决？

问题场景 #

执行Laravel数据库迁移，意图删除pallet_distributions表的rental_purchase_order_id外键约束并删除该列，同时新增origin_rental_purchase_order_id和destination_rental_purchase_order_id两个可空无符号大整数外键列，触发如下SQL错误：

Syntax error or access violation: 1091 Can't DROP 'pallet_distributions_rental_purchase_order_id_foreign'; check that column/key exists (SQL: alter table pallet_distributions drop foreign key pallet_distributions_rental_purchase_order_id_foreign)

对应的迁移代码：

<?php

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

class AddOriginAndDestinationRentalPurchaseOrderIdFieldsToPalletDistributionsTable extends Migration
{
    public function up()
    {
        Schema::table('pallet_distributions', function (Blueprint $table) {
            $table->dropForeign(['rental_purchase_order_id']);

            $table->dropColumn('rental_purchase_order_id');

            $table->unsignedBigInteger('origin_rental_purchase_order_id')->nullable();
            $table->unsignedBigInteger('destination_rental_purchase_order_id')->nullable();

            $table->foreign('origin_rental_purchase_order_id', 'origin_rental_purchase_order_fk_10726539')
                ->references('id')
                ->on('rental_purchase_orders');

            $table->foreign('destination_rental_purchase_order_id', 'destination_rental_purchase_order_fk_10746540')
                ->references('id')
                ->on('rental_purchase_orders');
        });
    }

    public function down()
    {
        Schema::table('pallet_distributions', function (Blueprint $table) {
            $table->dropForeign('origin_rental_purchase_order_fk_10726539');
            $table->dropForeign('destination_rental_purchase_order_fk_10746540');

            $table->dropColumn([
                'origin_rental_purchase_order_id',
                'destination_rental_purchase_order_id',
            ]);

            $table->unsignedBigInteger('rental_purchase_order_id')->nullable();

            $table->foreign('rental_purchase_order_id', 'rental_purchase_order_fk_10721477')
                ->references('id')
                ->on('rental_purchase_orders');
        });
    }
}

原因分析 #

报错核心是Laravel尝试删除的默认外键名pallet_distributions_rental_purchase_order_id_foreign在数据库中不存在，通常由以下情况导致：

• 当初创建rental_purchase_order_id外键时指定了自定义名称（比如down方法里的rental_purchase_order_fk_10721477），而非Laravel自动生成的默认名称；
• 之前的迁移操作已修改过该外键，导致默认名称对应的约束已被删除。

解决方案 #

步骤1：确认数据库中实际的外键名称 #

执行SQL语句查看表的创建语句，定位rental_purchase_order_id对应的外键约束名：

SHOW CREATE TABLE pallet_distributions;

在返回的Create Table字段中，找到类似CONSTRAINT xxx FOREIGN KEY (rental_purchase_order_id) REFERENCES rental_purchase_orders(id)的行，其中xxx就是实际的外键名称（比如down方法里的rental_purchase_order_fk_10721477）。

步骤2：修改迁移代码的up方法 #

将dropForeign的参数改为实际的外键名称，修改后的up方法示例：

public function up()
{
    Schema::table('pallet_distributions', function (Blueprint $table) {
        // 使用实际外键名称删除约束
        $table->dropForeign('rental_purchase_order_fk_10721477');

        $table->dropColumn('rental_purchase_order_id');

        $table->unsignedBigInteger('origin_rental_purchase_order_id')->nullable();
        $table->unsignedBigInteger('destination_rental_purchase_order_id')->nullable();

        $table->foreign('origin_rental_purchase_order_id', 'origin_rental_purchase_order_fk_10726539')
            ->references('id')
            ->on('rental_purchase_orders');

        $table->foreign('destination_rental_purchase_order_id', 'destination_rental_purchase_order_fk_10746540')
            ->references('id')
            ->on('rental_purchase_orders');
    });
}

额外优化（可选） #

如果不确定外键是否存在，可在删除前添加判断避免报错：

if (Schema::hasForeign('pallet_distributions', 'rental_purchase_order_fk_10721477')) {
    $table->dropForeign('rental_purchase_order_fk_10721477');
}

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