Framework: Dropping a foreign constraint with $table->dropForeign() leaves a key on the table

[isometriq]

• Laravel Version: 5.4.35
• PHP Version: 7.0.13
• Database Driver & Version: MariaDB 10.1.19

Description:

When using $table->dropForeign(...) in a migration, a key seems to stay in the table, like so:

KEY `table_field_foreign` (`field`)

Is that normal? ..there are no problems if the foreignkey is added again, but this key will linger.

Steps To Reproduce:

In a migration, use this in the up():

        Schema::table('table2', function (Blueprint $table) {
            $table->foreign('table2field')->references('id')->on('table1');
        });

..and this in the down():

        Schema::table('table2', function (Blueprint $table) {
            $table->dropForeign(['table2field']);
        });

Run the migration, then rollback.
Then using a database tool, check keys, you should find on table2:

KEY `table2_table2field_foreign` (`table2field`)

[dwightwatson]

Why are you using an array in the call to dropForeign? Also, from memory I think you need to actually pop in the full name of the key you want to drop, not just the column name.

[sisve]

It looks like you're talking about an index being left in the table. Mysql creates an index for your foreign key if there isn't already one present. Could it be this automatically created index you're seeing?

Try creating an index on the column manually, with a odd name, create and drop the foreign key, and verify that nothing is left of the foreign key.

[isometriq]

@dwightwatson check the docs here, at the end:
https://laravel.com/docs/5.4/migrations#indexes
This is where the array usage came from.

[isometriq]

In the migration down(), I can cleanup without errors with this:

$table->dropForeign(['table2field']);
$table->dropIndex(['table2field']);

..so dropping the constraint and explicitly the key (which is an index apparently). This does not caused errors during rollback

[isometriq]

@sisve Yes I confirm. It seems that mysql is the one creating the index automatically. It happens only when creating a constraint and there is no index already (with the same name?).

For me ..it's mystery solved.

[isometriq]

For reference:
https://stackoverflow.com/questions/304317/does-mysql-index-foreign-key-columns-automatically
https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html

[JohnDavisSDS]

This is kind of old, but I thought I'd point out that $table->dropIndex(['table2field']); will not drop your index either, it will try to drop an index named 'table2_table2field_index', not 'table2_table2field_foreign'. See the createIndexName method in Blueprint. Unfortunately the only way to drop that index is with the full key name unless you want to use reflection to make that createIndexName accessible.

[riotCode]

I just came to find that

         Schema::table('users', function (Blueprint $table) {
            $table->dropForeign(['role_id']);
            $table->integer('role_id')->change();
        });

doesn't work, on migrate:rollback you'll get an error that the change cannot be done because the foreign key still exists.

Finally got this to work

        Schema::table('users', function (Blueprint $table) {
            $table->dropForeign('users_role_id_foreign');
            $table->dropIndex('users_role_id_foreign');
        });

        // Conflict with change on same Blueprint instance
        Schema::table('users', function (Blueprint $table) {
            $table->integer('role_id')->change();
        });

Strange

[marijnz0r]

I encountered this bug as well, I think this is something that needs to be fixed.

[anchetaWern]

Still encountered this issue on laravel 5.8 so I used both dropForeign() and dropIndex() methods in the migration.

[guillaumecardon]

Still encoutered this issue on laravel 7.11
Bonus, if you change the type on your column to be eligible to a foreignkey, you can't rollback the type on "down" method without error :

```
public function up()
{
Schema::table('shows', function (Blueprint $table) {

        //At this point, the column "coloralert" is an integer 11 signed
        $table->foreignId('coloralert')->change();

        //At this point, the colum "coloralert" is a big integer 20 unsigned
        $table->foreign('coloralert')->references('id')->on('leds_panels');

    });
}


public function down()
{
    Schema::table('shows', function (Blueprint $table) {

        $table->dropForeign('shows_coloralert_foreign');            
        $table->dropIndex('shows_coloralert_foreign');          

        $table->integer('coloralert')->change(); //Instruction failed !
    }); 
}

```

[sisve]

I believe that the cause is already proven to my how mysql works. If you create a foreign key on a column without an index, mysql will create one automatically for you. That index is then still there when you drop the foreign key. There is no way to create a foreign key in mysql without having an index on the column.

The workaround is to add an index yourself, before adding the foreign key. You still end up with an index, but this time you know it's there, and you know to drop it when removing the foreign key.

[Monica-Ibtikar]

dropping foreign with index worked with me :
$table->dropForeign('orders_address_id_foreign'); $table->dropIndex('orders_address_id_foreign');