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?

Solution 1

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

Solution 2

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.

Solution 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.

Solution 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.

Solution 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.

View more solutions

Comments

[rich remer]

Does not work in Laravel 4.2. <foreign_table_name> is not part of the key name. It works with just <table_name>_<column_name>_foreign.

[Josh Bruce]

The accepted answer did not work for me; however, this one did. Cheers.

[Latheesan]

I used it in laravel 4.2 and still do, it works for me.

[Marco Pallante]

The accepted answer works too: you have to use the right index name convention. But this is the problem with that answer too: you have to remember the naming scheme for indexes, while this solution do it automatically! I always used the other way, and always complained about how unpractical it was. Now I'm immediately switching to this solution. Thank you very much!

[simonhamp]

Awesome trick. I've been doing it the long way like a sucker. Laravel could really use some help on the docs. I may take up the challenge...

[Yahya Uddin]

The <table_name>_<column_name>_foreign convention still seems to work for 5.1

[SilithCrowe]

Worked for me in Laravel 5.0. Thanks so much, Alex!

[Son Tran]

Worked like a charm in Laravel 5.2.

[Robin van Baalen]

This is a neat trick. Way friendlier than remembering the foreign key naming convention (which might change in the future). Like @ronin1184 said, works perfectly in Laravel 5.2

[Picrasma]

Apparently, after dropping the constraint on the relationship, you have to drop the column too. i think the documentation should have included that too because one can easily assume dropForeign will also delete the column. thanks for the sharing. laravel.com/docs/5.0/schema#dropping-columns

[Mark Karavan]

This works fine, I just found it to be less intuitive than surrounding the table in brackets as @Alex suggested.

[Robin Valk]

This answer also works in Laravel 4.2, and it is much easier to remember. Thanks!

[haris]

Works well in Laravel 5.3 too 😎

[Arian Acosta]

Works on 5.4! Checked the documentation and this appears since 5.1: "you may pass an array value which will automatically use the conventional constraint name when dropping". laravel.com/docs/5.1/migrations#foreign-key-constraints

[Pierre]

Thanks my friend, adding the column name in an array works for me.

[Zarul Izham]

Working flawlessly on Laravel 5.7!

[Soulriser]

This only works if you created the foreign keys with Laravel to begin with. In my case the foreign keys had been created long before Laravel entered the picture and across multiple databases which had auto-generated different foreign key names. I'm correcting this by explicitly dropping and then re-creating the FKs with new names so I can easily run FK related migrations.

[Aleksandar]

If anyone was wondering, indexes that MySQL automatically creates for foreign keys are dropped when the columns are. No need to drop them manually with $table->dropIndex('column_name').

[Aleksandar]

If anyone was wondering, indexes that MySQL automatically creates for foreign keys are dropped when the columns are. No need to drop them manually with $table->dropIndex('column_name').

[Aleksandar]

If anyone was wondering, indexes that MySQL automatically creates for foreign keys are dropped when the columns are. No need to drop them manually with $table->dropIndex('column_name').

[Akshay K Nair]

ty, i wanted Schema::table('offices', function (Blueprint $table) {} so bad...

[Sumit Wadhwa]

for some reason laravel doesn't prefix table name and postfix 'foreign' in dropForeign. so i think it should be: $table->dropForeign(['offices_country_id_foreign']);

[Sumit Wadhwa]

Also don't forget prefixes. Laravel desn't add those automatically either.