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

Question

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.

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)

[PDOException]
SQLSTATE[HY000]: General error: 1025 Error on rename of './dev_iwms_reboot/despatch_discrepancies' to './dev_iwms_reboot/#sql2-67c-17c464' (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.

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

Answer 1

You can use this:

Schema::table('despatch_discrepancies', function (Blueprint $table) {
    $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.

Answer 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:

<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:

<table_name>_<column_name>_foreign

Update:

Larave > 8.x introduces a new function

dropConstrainedForeignId('pick_detail_id');

This will delete the column as well as the foreign key of the column

Answer 3

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:

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.

Answer 4

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.

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:

[local table]_[foreign key field]_foreign

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

Answer 5

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

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

[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:

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.

Answer 6

on laravel 8 use dropConstrainedForeignId (https://github.com/laravel/framework/pull/34806)

<?php

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

class AddAddressFieldsInEventTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        
        Schema::table('events', function (Blueprint $table) {
            $table->bigInteger('address_id')->nullable();

            $table->foreign('address_id')
                ->references('id')
                ->on('addresses')
                ->onDelete('cascade');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('events', function (Blueprint $table) {
            $table->dropConstrainedForeignId('address_id');
            $table->dropColumn('address_id');
        });
    }
}

Answer 7

In Laravel > 8.x you can use the following code to add/remove foreign keys. You will need to drop the foreign key before dropping the column, as otherwise, the foreign key could cause the error your encountering.

Add foreign key

 $table->foreignId('pick_id')->constrained('picks')->cascadeOnUpdate()
->cascadeOnDelete();

Drop foreign key

  $table->dropConstrainedForeignId('pick_id');

Answer 8

You can disable relation id first

Schema::disableForeignKeyConstraints();