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.

score 0 · Answer 1 · answered Apr 11 '23 at 18:05

0

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.

answered Apr 11 '23 at 18:05

ndm

59,784
9
71
110

Right, in this case as it creates the index anyway, in the method below I first remove these indexes and then I drop the foreign key and finally I remove all the columns, according to the code below. But the error still follows `code´ public function down() { $this->table('mod_chargeback') ->removeIndex(['client_id']) ->removeIndex(['invoice_id']) ->removeIndex(['transaction_id']) ->dropForeignKey('transaction_id') ->removeColumn('transaction_id') `code´ – Apr 11 '23 at 18:20
@luankobs You are again removing the index before dropping the foreign key, only now you're removing it _explicitly_, instead of _implicitly_ when removing the column. – ndm Apr 11 '23 at 18:44

Write drop foreign keys with Phinx

1 Answers1