Renaming a foreign key doesn't update the foreign key in MySQL with InnoDB

[KyleMac]

The index seems to be updated but not the actual fk that is used to follow cascades and so on. This actually doesn't seem to cause too many problems until you try to do another alteration to the same table and you start getting seemingly unrelated errors about incorrect foreign keys.

[KyleMac]

Here's what I had to do to repair my table:

if db.backend_name == 'mysql':
    db.drop_foreign_key('shop_order_transaction', 'order_processor_id')
    db.execute('ALTER TABLE shop_order_transaction ADD CONSTRAINT '
               'order_id_fk_to_order FOREIGN KEY (order_id) '
               'REFERENCES shop_order(id)')

[andrew]

Also see #243.

[merchant.michael@…]

I'm having this issue as well. Would be happy to help provide information if needed.

[andrew]

See ticket #679 for a similar issue and related patch

[vijay.krishna.ramesh@…]

Looking at the 0.7.2 source, I noticed that db.alter_column does a self.delete_foreign_key at the beginning of the operation, and then a self.execute(self. foreign(...)) at the end - this avoids the MySQL issue cropping up with alter_column. Why not just do something similar for db.rename_column?

[vijay.krishna.ramesh@…]

In the meantime, my fix (to rename a foreign key, including it's reverse lookup related_name) was the following (renaming lib_studentgroup.assessment_instance_id to lib_studentgroup.assessment_instance_deprecated_id, including the foreign key constraints):

db.drop_foreign_key('lib_studentgroup', 'assessment_instance_id')
db.rename_column('lib_studentgroup', 'assessment_instance_id', 'assessment_instance_deprecated_id')
db.alter_column('lib_studentgroup', 'assessment_instance_deprecated', models.ForeignKey(blank=True, related_name='student_group_deprecated', null=True, to=orm['lib.AssessmentInstance']),explicit_name=False)