Ticket #466 (assigned defect)

Opened 5 years ago

Last modified 4 years ago

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

Reported by: KyleMac Owned by: andrew
Priority: major Milestone: 1.0
Component: migrations Version: 0.7
Keywords: Cc:


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.

Change History

comment:1 Changed 5 years ago by 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)')

comment:2 Changed 5 years ago by andrew

  • Status changed from new to assigned
  • Milestone set to 0.7.2

Also see #243.

comment:3 Changed 5 years ago by andrew

  • Milestone changed from 0.7.2 to 0.7.3

comment:4 Changed 5 years ago by andrew

  • Milestone changed from 0.7.3 to 1.0

comment:5 Changed 5 years ago by merchant.michael@…

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

comment:6 Changed 5 years ago by andrew

See ticket #679 for a similar issue and related patch

comment:7 follow-up: ↓ 8 Changed 4 years ago by 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?

comment:8 in reply to: ↑ 7 Changed 4 years ago by 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)

Note: See TracTickets for help on using tickets.