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

I feel this could be considered a duplicate of https://stackoverflow.com/questions/44188450/dropping-foreign-keys-in-laravel-migration — Jeremy Anderson, Mar 17 '18 at 12:39

Gonzalo · Answer 1 · 2018-03-17T12:37:54.310

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');
    });
}

Will try and let you know thanks i tried sending as an array but dint work. I had missed dropIndex. — Jigs1212, Mar 17 '18 at 12:36

score 0 · Answer 2 · answered Mar 17 '18 at 09:42

0

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

answered Mar 17 '18 at 09:42

Levente Orbán

67
13

i can drop the foriegn key constraint and then drop the column i am looking for that solution through migration command. – Jigs1212 Mar 17 '18 at 09:44
Okey, so first drop the pending columns/tables in your migration. – Levente Orbán Mar 17 '18 at 09:48
1

You don't understand. Drop first the `fk_int_business_id`, column, but you can't, beacuse there is other dependecy in my opinion. So first clear the dependecy of the `fk_int_business_id` and after drop the table what you want. – Levente Orbán Mar 17 '18 at 10:10
can u see this `Schema::disableForeignKeyConstraints();` can u ? – Jigs1212 Mar 17 '18 at 10:11
I'm pretty sure Levente is likely to turn out correct, the problem seems most likely to be a foreign key constraint on the database side. – Jeremy Anderson Mar 17 '18 at 12:43

Drop foriegn key in laravel

2 Answers2