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:
[IlluminateDatabaseQueryException]
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 tabledespatch_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?
Advertisement
Answer
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