Drop foriegn key in laravel

Question

I am trying to drop foreign key using migration.

Here is my code

public function up()
    {
        Schema::table('tbl_social_media_links', function (Blueprint $table) {
            $table->renameColumn('vchr_link', 'vchr_social_media_link');
            $table->dropColumn('vchr_social_media_name');
            $table->integer('fk_int_business_id')->unsigned()->after('pk_int_sm_id');
            $table->foreign('fk_int_business_id')->references('pk_int_business_id')
                ->on('tbl_business_details')->onDelete('cascade');
            $table->integer('int_social_media_type')->after('fk_int_business_id');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('tbl_social_media_links', function (Blueprint $table) {
            Schema::disableForeignKeyConstraints();
            $table->string('vchr_social_media_name')->after('pk_int_sm_id');
            $table->dropColumn('fk_int_business_id');
            $table->dropColumn('int_social_media_type');
            $table->renameColumn('vchr_social_media_link', 'vchr_link');
            Schema::enableForeignKeyConstraints();
        });
    }

ALSO TRIED $table->dropForiegn('fk_int_business_id');

I keep getting errors like

General error: 1553 Cannot drop index 'tbl_social_media_links_fk_int_business_id_foreign': needed in a foreign key constraint (
  SQL: alter table `tbl_social_media_links` drop `fk_int_business_id`)

Can someone please help me get it to work.

I even tried with sql to drop it but it says Can't DROP 'fk_int_business_id'; check that column/key exists

Answer 1

This is how I would do it:

/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    Schema::table('tbl_social_media_links', function (Blueprint $table) {
        $table->foreign('fk_int_business_id')
            ->references('pk_int_business_id')
            ->on('tbl_business_details')
            ->onDelete('cascade');;
    });
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::table('tbl_social_media_links', function (Blueprint $table) {
        $table->dropForeign(['fk_int_business_id']);
    });
}

Notice that I'm sending using a string array as an argument in dropForeign instead of a string, because the behaviour is different. With an array, you can use the column name, but with a string, you need to use the key name instead.

However, I found out that, even when it removes the foreign key, an index key remains, so this would solve it:

/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    Schema::table('tbl_social_media_links', function (Blueprint $table) {
        $table->foreign('fk_int_business_id')
            ->references('pk_int_business_id')
            ->on('tbl_business_details')
            ->onDelete('cascade');;
    });
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::table('tbl_social_media_links', function (Blueprint $table) {
        $index = strtolower('tbl_social_media_links'.'_'.implode('_', ['fk_int_business_id']).'_foreign');
        $index = str_replace(['-', '.'], '_', $index);

        $table->dropForeign($index);
        $table->dropIndex($index);
    });
}

Also notice that, in this case, I'm generating the key name instead of using an array, as I couldn't find any other way to drop the index key with just the column name.

On the other hand, be aware that you may need to split the statements to drop the column:

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::table('tbl_social_media_links', function (Blueprint $table) {
        $index = strtolower('tbl_social_media_links'.'_'.implode('_', ['fk_int_business_id']).'_foreign');
        $index = str_replace(['-', '.'], '_', $index);

        $table->dropForeign($index);
        $table->dropIndex($index);
    });

    Schema::table('tbl_social_media_links', function (Blueprint $table) {
        $table->dropColumn('fk_int_business_id');
    });
}

Answer 2

You can't drop it until other table, or column depends on it.

Here is some similar question: MySQL Cannot drop index needed in a foreign key constraint