id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
30591	"MySQL: 1215 - ""Cannot add foreign key constraint"" when altering type of unique field referenced by ForeignKey."	Cornelis Poppema	Adnan Umer	"I have two models and a ForeignKey. In this project I'm still using django 1.11 (still busy migrating to python3) but I have reproduced this in django 2.2.2

Models are Call and Client. Call has a ForeignKey to a PositiveIntegerField on Client. I'm changing the type of this field to a CharField, but django/mysql cannot drop or add constraints.

I found this issue: https://code.djangoproject.com/ticket/28305. I had hoped I was still using a version of 1.11 that didn't include the backport but unfortunately for me I did.
Before creating this ticket I also came across https://code.djangoproject.com/ticket/30152 which is also related to my initial findings. As https://code.djangoproject.com/ticket/30152#comment:10 says, the internals are checking for relations that might have constraints, but in 30152's and my case it doesn't find the relation. As a result the foreign key constraint isn't dropped resulting in the error:


{{{
django.db.utils.OperationalError: (1833, ""Cannot change column 'code': used in a foreign key constraint 'myapp_call_clientcode_907d4acf_fk_myapp_client_code' of table 'test_test.myapp_call'"")
}}}


After either applying the patch https://code.djangoproject.com/attachment/ticket/30152/testcase_and_rough_fix.patch or simply (temporarily) removing related_name='+' I get past this OperationError. Instead I am confronted with:


{{{
django.db.utils.IntegrityError: (1215, 'Cannot add foreign key constraint')
}}}


From what I've read about MySQL constraints this is caused by the different field types. Changing the type of the original field doesn't change the type of the foreign key field and the constraint cannot be re-added after dropping it.

I created a project on github: https://github.com/cpoppema/django-migrate-constraint-bug, ignore the master-branch for now.

Branch with original issue: https://github.com/cpoppema/django-migrate-constraint-bug/tree/cannot-change-column-used-in-a-foreign-key-constraint
Branch with issue without related_name='+': https://github.com/cpoppema/django-migrate-constraint-bug/tree/cannot-add-foreign-key-constraint

Known workaround that I've come up with is:

run a migration to change ForeignKey on Call to PositiveIntegerField
run a migration to change the PositiveIntegerField on Client to CharField
run a migration to change the PositiveIntegerField on Call to ForeignKey

With this, both fields end up as a CharField. I'd rather have Django generate either multiple migrations or do an ALTER TABLE for related fields."	Bug	closed	Migrations	dev	Normal	fixed			Accepted	1	0	0	0	0	0