php 删除带有外键 Laravel 错误的列：一般错误：1025 重命名错误

Dropping column with foreign key Laravel error: General error: 1025 Error on rename

提问by Latheesan

I've created a table using migration like this:

我已经使用这样的迁移创建了一个表：

public function up()
{
    Schema::create('despatch_discrepancies',  function($table) {
        $table->increments('id')->unsigned();
        $table->integer('pick_id')->unsigned();
        $table->foreign('pick_id')->references('id')->on('picks');
        $table->integer('pick_detail_id')->unsigned();
        $table->foreign('pick_detail_id')->references('id')->on('pick_details');
        $table->integer('original_qty')->unsigned();
        $table->integer('shipped_qty')->unsigned();
    });
}

public function down()
{
    Schema::drop('despatch_discrepancies');
}

I need to change this table and drop the foreign key reference & column pick_detail_idand add a new varchar column called skuafter pick_idcolumn.

我需要更改此表并删除外键引用 & 列pick_detail_id并添加一个名为skuafterpick_id列的新 varchar列。

So, I've created another migration, which looks like this:

因此，我创建了另一个迁移，如下所示：

public function up()
{
    Schema::table('despatch_discrepancies', function($table)
    {
        $table->dropForeign('pick_detail_id');
        $table->dropColumn('pick_detail_id');
        $table->string('sku', 20)->after('pick_id');
    });
}

public function down()
{
    Schema::table('despatch_discrepancies', function($table)
    {
        $table->integer('pick_detail_id')->unsigned();
        $table->foreign('pick_detail_id')->references('id')->on('pick_details');
        $table->dropColumn('sku');
    });
}

When I run this migration, I get the following error:

运行此迁移时，出现以下错误：

[Illuminate\Database\QueryException]
SQLSTATE[HY000]: General error: 1025 Error on rename of './dev_iwms_reboot/despatch_discrepancies' to './dev_iwms_reboot/#sql2-67c-17c464' (errno: 152) (SQL: alter table despatch_discrepanciesdrop foreign key pick_detail_id)

[PDOException]
SQLSTATE[HY000]: General error: 1025 Error on rename of './dev_iwms_reboot/despatch_discrepancies' to './dev_iwms_reboot/#sql2-67c-17c464' (errno: 152)

[Illuminate\Database\QueryException]
SQLSTATE[HY000]：一般错误：1025 将“./dev_iwms_reboot/despatch_discrepancies”重命名为“./dev_iwms_reboot/#sql2-67c-17c464”时出错（errnoSQL：152）（ despatch_discrepancies删除外键 pick_detail_id)

[PDOException]
SQLSTATE[HY000]：一般错误：1025 将“./dev_iwms_reboot/despatch_discrepancies”重命名为“./dev_iwms_reboot/#sql2-67c-17c464”时出错（错误号：152）

When I try to reverse this migration by running php artisan migrate:rollbackcommand, I get a Rolled backmessage, but it's not actually doing anything in the database.

当我尝试通过运行php artisan migrate:rollback命令来反转此迁移时，我收到一条Rolled back消息，但它实际上并未在数据库中执行任何操作。

Any idea what might be wrong? How do you drop a column that has a foreign key reference?

知道可能有什么问题吗？如何删除具有外键引用的列？

采纳答案by Latheesan

It turns out; when you create a foreign key like this:

事实证明；当您创建这样的外键时：

$table->integer('pick_detail_id')->unsigned();
$table->foreign('pick_detail_id')->references('id')->on('pick_details');

Laravel uniquely names the foreign key reference like this:

Laravel 像这样唯一地命名外键引用：

<table_name>_<foreign_table_name>_<column_name>_foreign
despatch_discrepancies_pick_detail_id_foreign (in my case)

Therefore, when you want to drop a column with foreign key reference, you have to do it like this:

因此，当您想删除带有外键引用的列时，您必须这样做：

$table->dropForeign('despatch_discrepancies_pick_detail_id_foreign');
$table->dropColumn('pick_detail_id');

Update:

更新：

Laravel 4.2+ introduces a new naming convention:

Laravel 4.2+ 引入了新的命名约定：

<table_name>_<column_name>_foreign

回答by Alex Pineda

You can use this:

你可以使用这个：

$table->dropForeign(['pick_detail_id']);
$table->dropColumn('pick_detail_id');

If you take a peak at dropForeign source, it will build the foreign key index name for you if you pass the column name as an array.

如果您在 dropForeign 源上获取峰值，如果您将列名作为数组传递，它将为您构建外键索引名称。

回答by Afraz Ahmad

I had multiple foreign keys in my table and then I had to remove foreign key constraints one by one by passing column name as index of the array in down method:

我的表中有多个外键，然后我不得不通过在 down 方法中传递列名作为数组的索引来一个一个地删除外键约束：

public function up()
{
    Schema::table('offices', function (Blueprint $table) {
        $table->unsignedInteger('country_id')->nullable();
        $table->foreign('country_id')
            ->references('id')
            ->on('countries')
            ->onDelete('cascade');

        $table->unsignedInteger('stateprovince_id')->nullable();
        $table->foreign('stateprovince_id')
            ->references('id')
            ->on('stateprovince')
            ->onDelete('cascade');
        $table->unsignedInteger('city_id')->nullable();
        $table->foreign('city_id')
            ->references('id')
            ->on('cities')
            ->onDelete('cascade');
    });
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::table('offices', function (Blueprint $table) {
        $table->dropForeign(['country_id']);
        $table->dropForeign(['stateprovince_id']);
        $table->dropForeign(['city_id']);
        $table->dropColumn(['country_id','stateprovince_id','city_id']);
    });
} 

Using below statement does not work

使用以下语句不起作用

$table->dropForeign(['country_id','stateprovince_id','city_id']); 

Because dropForeign does not consider them seperate columns that we want to remove. So we have to drop them one by one.

因为 dropForeign 不认为它们是我们要删除的单独列。所以我们必须一一放下它们。

回答by Harry Bosh

Pass an array with col name

传递一个带有 col 名称的数组

$table->dropForeign(['user_id']);

回答by DirtyBirdNJ

The key (for me) to solving this was to make sure that the $table->dropForeign() command was being passed the right relationship name, not necessarily the column name. You do notwant to pass the column name, as would be much more intuitive IMHO.

解决这个问题的关键（对我来说）是确保 $table->dropForeign() 命令被传递正确的关系名称，不一定是列名称。你不是想通过列名，会更直观恕我直言。

What worked for me was:

对我有用的是：

$table->dropForeign('local_table_foreign_id_foreign');
$table->column('foreign_id');

So the string I passed to dropForeign() that worked for me was in the format of:

所以我传递给 dropForeign() 对我有用的字符串格式为：

[local table]_[foreign key field]_foreign

[本地表]_ [外键字段]_foreign

If you have access to a tool like Sequel Pro or Navicat, being able to visualize those will be very helpful.

如果您可以使用 Sequel Pro 或 Navicat 之类的工具，能够将它们可视化将非常有帮助。

回答by Gus

Something that occurred to me was that I didn't know where to put the Schema::tableblock.

我突然想到的是，我不知道把Schema::table积木放在哪里。

Later I discovered that the key is on the SQL error:

后来我发现关键是在SQL错误上：

[Illuminate\Database\QueryException]
SQLSTATE[23000]: Integrity constraint violation: 1217 Cannot delete or update a parent row: a foreign key constraint fails (SQL: drop table if exists `lu_benefits_categories`)

So the Schema::tableblock needs to go in the down()function of the lu_benefits_categoriesmigration and before the Schema::dropIfExistsline:

所以Schema::tableblock需要down()在lu_benefits_categoriesmigration的函数中和之前的Schema::dropIfExists行：

public function down()
{
    Schema::table('table', function (Blueprint $table) {
        $table->dropForeign('table_category_id_foreign');
        $table->dropColumn('category_id');
    });
    Schema::dropIfExists('lu_benefits_categories');
}

After that, the php artisan migrate:refreshor php artisan migrate:resetwill do the trick.

之后，php artisan migrate:refreshorphp artisan migrate:reset将起作用。