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