Write drop foreign keys with Phinx

Question

I'm trying to write this migration using only up and down method, but I'm getting this error.

SQLSTATE[HY000]: General error: 1553 Cannot drop index 'transaction_id': needed in a foreign key constraint

<?php

use \Test\Migrations\Migration;

class ModifyTableChargeback extends Migration

{
    public function up()
    {
        $table = $this->table('mod_chargeback');
        $table->addColumn('client_id', 'integer', ['after' => 'id'])
            ->addIndex('client_id')
            ->addColumn('invoice_id', 'integer', ['after' => 'client_id'])
            ->addIndex('invoice_id')
            ->addColumn('transaction_id', 'integer', ['after' => 'invoice_id'])
            ->addColumn('date_paid', 'datetime', ['null' => true, 'after' => 'result'])
            ->addColumn('date', 'date', ['null' => true, 'after' => 'date_paid'])
            ->addColumn('reason', 'string', ['null' => true, 'after' => 'date'])
            ->addColumn('issuer_message', 'string', ['null' => true, 'after' => 'reason'])
            ->addForeignKey('transaction_id', 'mod_transactions', 'id'
            ->update();
    }

    public function down()
    {
        $this->table('mod_chargeback')
            ->removeIndex('client_id')
            ->removeIndex('invoice_id')
            ->removeColumn('client_id')
            ->removeColumn('invoice_id')
            ->removeColumn('transaction_id')
            ->removeColumn('date_paid')
            ->removeColumn('date')
            ->removeColumn('reason')
            ->removeColumn('issuer_message')
            ->dropForeignKey('transaction_id')
            ->update();
    }
}

I tried to add the constraint in the $options parameter but without success The up method is working, but in the drop it doesn't remove the foreign key, I've tried to reorder the drop orders but without success too.

Answer 1

MySQL requires columns of a foreign key constraint to be indexed, if no index exists, one will be created. Dropping the column would drop the index, hence the error message.

The message can be a bit misleading if you don't know about the implicit index. This changed in MySQL 8 it seems, where it would instead mention the column:

Cannot drop column 'transaction_id': needed in a foreign key constraint ...

Conclusion, drop the foreign key before removing the columns.