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

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_id and add a new varchar column called sku after pick_id column.

我需要更改此表并删除外键引用&列pick_detail_id，并在pick_id列之后添加一个名为sku的新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_discrepancies drop foreign key pick_detail_id)

[解释\数据库\QueryException] SQLSTATE[HY000]:一般错误:重命名为1025错误。/ dev_iwms_reboot despatch_discrepancies’”。/ dev_iwms_restart /#sql2-67c-17c464' (errno: 152) (SQL: alter table despatch_differences drop 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)

[PDOException] SQLSTATE[HY000]:一般错误:重命名为'的1025错误。/ dev_iwms_reboot despatch_discrepancies’”。/ dev_iwms_reboot / # sql2 - 67 c - 17 c464”(errno:152)

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

当我试图通过运行php artisan migration:rollback命令来逆转这种迁移时，我得到一个回滚消息，但它实际上并没有在数据库中执行任何操作。

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

你知道什么地方不对劲吗?如何删除具有外键引用的列?

6 个解决方案

#1

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 source设置一个峰值，如果将列名作为数组传递，它将为您构建外键索引名。

#2

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

#3

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 not want to pass the column name, as would be much more intuitive IMHO.

解决这个问题的关键(对我来说)是确保$table->dropForeign()命令被传递了正确的关系名称，而不一定是列名。您不希望传递列名，因为这将是更直观的IMHO。

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之类的工具，那么可视化这些工具将非常有帮助。

#4

Pass an array with col name

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

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

#5

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

我想到的是，我不知道将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::table block needs to go in the down() function of the lu_benefits_categories migration and before the Schema::dropIfExists line:

因此，Schema: table块需要进入lu_benefit its_categories迁移的down()函数，在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:refresh or php artisan migrate:reset will do the trick.

之后，php artisan迁移:refresh或php artisan迁移:reset就可以了。

#6

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不会将它们视为我们想要删除的分离列。所以我们必须一个一个地放弃。