Cannot remigrate migration after migration rollback Laravel 5.2

Question

Good day, I'm new to laravel I was doing a migration rollback and it was successfully done

Rolled back: 2018_02_22_172102_adding_fk_constrains_products_to_product_types_and_service_sub_types_table

But when I try to re-migrate I encountered this error below. BTW I don't want to drop the column because it already existed and I don't want to lose the existing data in that column. I only want to add constraint between those tables

[Illuminate\Database\QueryException] SQLSTATE[23000]: Integrity constraint violation: 1022 Can't write; duplicate key in table '#sql-2fc8_17c' (SQL: alter table products add constraint products_product_type_id_foreign foreign key (product_type_id) references product_types (id) on update cascade)

[PDOException] SQLSTATE[23000]: Integrity constraint violation: 1022 Can't write; duplicate key in table '#sql-2fc8_17c'

Table     Non_unique  Key_name                            Seq_in_index  Column_name
--------  ----------  ----------------------------------  ------------  -------------------
products           0  PRIMARY                                        1  id             
products           1  products_product_type_id_index                 1  product_type_id  
products           1  products_service_sub_type_id_index             1  service_sub_type_id

this my migration code

public function up()
{

    Schema::table('products',function (Blueprint $table){
        $table->integer('product_type_id')->unsigned()->index()->change();
        $table->foreign('product_type_id')->references('id')->on('product_types')->onUpdate('cascade');

        $table->integer('service_sub_type_id')->nullable()->unsigned()->index()->change();
        $table->foreign('service_sub_type_id')->references('id')->on('service_sub_types')->onUpdate('cascade');

    });
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{

    Schema::table('products', function(Blueprint $table){
        $table->dropForeign(['product_type_id']);
        $table->dropForeign(['service_sub_type_id']);
    });

}

you might need to check your MySQL database to see if the the foreign key actually exist, else you can try to use Schema::disableForeignKeyConstraints(); — har2vey
Schema::disableForeignKeyConstraints(); I added this code in my down() method but the error still persist. — Novice
Yeah. it exist in my DB. I added the result of this query in my updated question SHOW INDEXES FROM products; — Novice
well you see the issue is that the key name is actually 'products_product_type_id_index', laravel naming convention for dropping foreign key is to use _foreign suffix, so in your case it should be product_type_id_foreign — har2vey

Novice Novice · Accepted Answer · 2018-02-27T03:09:53

I just remove the following code in my migration

$table->integer('product_type_id')->unsigned()->index()->change();

$table->integer('service_sub_type_id')->nullable()->unsigned()->index()->change();

public function up()
{

   Schema::table('products',function (Blueprint $table){
      //$table->integer('product_type_id')->unsigned()->index()->change();
      $table->foreign('product_type_id')->references('id')->on('product_types')->onUpdate('cascade');

      //$table->integer('service_sub_type_id')->nullable()->unsigned()->index()->change();
      $table->foreign('service_sub_type_id')->references('id')->on('service_sub_types')->onUpdate('cascade');

});
}

when I re migrate it was successfully migrated..

Cannot remigrate migration after migration rollback Laravel 5.2

1 Answers