Reverse migration / drop foreign key for custom index name laravel migration

Question

So I have this migration

public function up()
    {
        Schema::table('xxx', function (Blueprint $table) {
            $table->foreign('aaa','bbb') // handle identifier name too long
                ->references('id')->on('aaa_table')->onDelete('cascade')->onUpdate('cascade');
        });
    }

I want create the down() function / reverse the migration for remove this foreign key.

What I have tried here:

public function down()
    {
        Schema::table('xxx', function (Blueprint $table) {
            // implement one line of all 4 possibilities
            $table->dropForeign(['aaa']);
            $table->dropForeign(['bbb']);
            $table->dropForeign(['aaa'], 'bbb');
            $table->dropForeign(['bbb'], 'aaa');
        });
    }

All giving the same error when I run rollback this migration file,

SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'zzz'; check that column/key exists

The database condition is still clean, no data (row) inserted. If I do it without the custom name assigned to second argument in foreign and short name case (no warning abount identifier name too long), the reverse migration runs smoothly.

OMR · Accepted Answer · 2020-09-17T04:58:57.280

according to laravel documentation

Alternatively, you may pass an array containing the column name that holds the foreign key to the dropForeign method. The array will be automatically converted using the constraint name convention used by Laravel's schema builder:

$table->dropForeign(['user_id']);

for our case, it should be:

$table->dropForeign(['aaa','bbb']);

but this will not be true for your case because of:

the method foreign take two parameters, one as columns names, other as foreign key name.

in your case, you use one column for relation and the other for naming!

  $table->foreign('aaa','bbb')

it should be:

  $table->foreign(['aaa','bbb'])

alternatively you could make it like:

 $table->foreign(['aaa','bbb'],'my_foreign_name')....

and in down() method

  $table->dropForeign('my_foreign_name');

I don't know why, but it still generates the same error – n00b123 Sep 17 '20 at 04:46 — n00b123, Sep 17 '20 at 04:46

score 0 · Answer 2 · answered Apr 13 '23 at 22:56

For those dealing with indexes

Let's say you created your foreign key column using following migration:

Schema::table('my_table', function (Blueprint $table) {
    $table->foreign('user_id')
        ->references('id')
        ->on('users')
        ->onDeleteCascade()
        ->onUpdateCascade();
});

With this migration, Laravel said to MySQL - Create foreign key constraint on column named user_id.

However, some databases, like MySQL will automatically create index for any newly created foreign key constraint. Please note difference between having index and constraint... they are two standalone DB elements!

Dropping foreign key constraint does not mean, foreign key index has been dropped as well! You need to drop it separately.

Reverse migration / drop foreign key for custom index name laravel migration

2 Answers2

For those dealing with indexes