CODEWITHSUNDEEP
phpmysqldoctrine-ormdoctrine
cuongle
cuongle

Reputation: 75316

Doctrine migration: adding and removing two foreign key columns to existing table is not working

I have the existing table pricing, on migration script I would like to add two new columns pricing_set_id and coe_id which both are foreign keys.

On up function, it runs perfectly without any errors, but when I run down function, it got the errors. My code as below.

On up function:

$pricingTable = $schema->getTable('pricing');
$pricingSetTable = $schema->getTable('pricing_set');

if (!$pricingTable->hasColumn('pricing_set_id')) {
    $pricingTable->addColumn('pricing_set_id', 'uuid')->setNotnull(false);
}

if (!$pricingTable->hasIndex('FK_pricing_set_idx')) {
    $pricingTable->addIndex(['pricing_set_id'], 'FK_pricing_set_idx');
}

if (!$pricingTable->hasForeignKey('FK_pricing_set')) {
    $pricingTable->addForeignKeyConstraint($pricingSetTable,
        ['pricing_set_id'],
        ['id'],
        ['onUpdate' => 'CASCADE', 'onDelete' => 'CASCADE'],
        'FK_pricing_set');
}

if (!$pricingTable->hasColumn('coe_id')) {
    $pricingTable->addColumn('coe_id', 'uuid')->setNotnull(false);
}

if (!$pricingTable->hasIndex('FK_pricing_coe_idx')) {
    $pricingTable->addIndex(['coe_id'], 'FK_pricing_coe_idx');
}

if (!$pricingTable->hasForeignKey('FK_pricing_coe')) {
    $pricingTable->addForeignKeyConstraint($schema->getTable('user'),
        ['coe_id'],
        ['id'],
        ['onUpdate' => 'CASCADE','onDelete' => 'CASCADE'], 
        'FK_pricing_coe');    
}

On down function:

$pricingTable = $schema->getTable('pricing');

if ($pricingTable->hasForeignKey('FK_pricing_set')) {
    $pricingTable->removeForeignKey('FK_pricing_set');
}

if ($pricingTable->hasColumn('pricing_set_id')) {
    $pricingTable->dropColumn('pricing_set_id');
}

if ($pricingTable->hasIndex('FK_pricing_set_idx')) {
    $pricingTable->dropIndex('FK_pricing_set_idx');
}

if ($pricingTable->hasForeignKey('FK_pricing_coe')) {
    $pricingTable->removeForeignKey('FK_pricing_coe');
}

if ($pricingTable->hasColumn('coe_id')) {
    $pricingTable->dropColumn('coe_id');
}

if ($pricingTable->hasIndex('FK_pricing_coe_idx')) {
    $pricingTable->dropIndex('FK_pricing_coe_idx');
}

The error I got:

 -> ALTER TABLE pricing DROP FOREIGN KEY FK_pricing_set
 -> DROP INDEX FK_pricing_set_idx ON pricing
 -> DROP INDEX FK_pricing_coe_idx ON pricing

Migration 20141031071812 failed during Execution. Error An exception occurred while executing 'DROP INDEX FK_pricing_coe_idx ON pricing': SQLSTATE[HY000]: General error: 1553 Cannot drop index 'FK_pricing_coe_idx': needed in a foreign key constraint.

I have noticed the order of SQL which Doctrine generates is not exactly the same as expected on Doctrine code. It removes the index FK_pricing_coe_idx without removing its foreign key. this might be the reason why it got error.

is there any way to work around for this thing?

Upvotes: 8

Views: 3167

Answers (1)

anupam.singhal
anupam.singhal

Reputation: 101

The error happens because the FK_pricing_coe foreign key still exists when the index is getting removed.

Upvotes: 1

Related Questions