Django : can't add foreign key constraint while migrate table in mysql

user_7874623 0
Viewed 2055

I have bunch of tables and tried to do migration which is change Charfield into manytomany field. However while I am doing this I encountered this error. What I trying to do was alter columns in the table that already has data, and create two new tables that related to new columns. I have no idea how I can solve this.

The error log is this.

    Operations to perform:
  Apply all migrations: food
Running pre-migrate handlers for application food 
Running pre-migrate handlers for application admin_tools
Running pre-migrate handlers for application theming
Running pre-migrate handlers for application menu
Running pre-migrate handlers for application dashboard
Running pre-migrate handlers for application admin
Running pre-migrate handlers for application auth
Running pre-migrate handlers for application contenttypes
Running pre-migrate handlers for application sessions
Running pre-migrate handlers for application rest_framework
Running migrations:
Applying fiid.0113_auto_20171127_1852...Traceback (most recent call last):
File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/django/db/backends/utils.py", line 65, in execute
 return self.cursor.execute(sql, params)
File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/django/db/backends/mysql/base.py", line 101, in execute
return self.cursor.execute(query, args)
File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/MySQLdb/cursors.py", line 250, in execute
self.errorhandler(self, exc, value)
File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
raise errorvalue
File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/MySQLdb/cursors.py", line 247, in execute
res = self._query(query)
 File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/MySQLdb/cursors.py", line 411, in _query
rowcount = self._do_query(q)
 File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/MySQLdb/cursors.py", line 374, in _do_query
db.query(q)
 File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/MySQLdb/connections.py", line 292, in query
 _mysql.connection.query(self, query)
_mysql_exceptions.IntegrityError: (1215, 'Cannot add foreign key constraint')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "manage.py", line 22, in <module>
execute_from_command_line(sys.argv)
File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/django/core/management/__init__.py", line 363, in execute_from_command_line
utility.execute()
File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/django/core/management/__init__.py", line 355, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
 File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/django/core/management/base.py", line 283, in run_from_argv
self.execute(*args, **cmd_options)
 File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/django/core/management/base.py", line 330, in execute
output = self.handle(*args, **options)
 File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/django/core/management/commands/migrate.py", line 204, in handle
fake_initial=fake_initial,
 File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/django/db/migrations/executor.py", line 115, in migrate
state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
 File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/django/db/migrations/executor.py", line 145, in _migrate_all_forwards
state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
 File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/django/db/migrations/executor.py", line 244, in apply_migration
state = migration.apply(state, schema_editor)
 File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/django/db/backends/base/schema.py", line 93, in __exit__
self.execute(sql)
 File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/django/db/backends/base/schema.py", line 120, in execute
cursor.execute(sql, params)
 File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/django/db/backends/utils.py", line 80, in execute
return super(CursorDebugWrapper, self).execute(sql, params)
  File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/django/db/backends/utils.py", line 65, in execute
return self.cursor.execute(sql, params)
 File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/django/db/utils.py", line 94, in __exit__
six.reraise(dj_exc_type, dj_exc_value, traceback)
 File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/django/utils/six.py", line 685, in reraise
raise value.with_traceback(tb)
 File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/django/db/backends/utils.py", line 65, in execute
return self.cursor.execute(sql, params)
 File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/django/db/backends/mysql/base.py", line 101, in execute
return self.cursor.execute(query, args)
 File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/MySQLdb/cursors.py", line 250, in execute
self.errorhandler(self, exc, value)
 File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
raise errorvalue
 File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/MySQLdb/cursors.py", line 247, in execute
res = self._query(query)
 File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/MySQLdb/cursors.py", line 411, in _query
rowcount = self._do_query(q)
 File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/MySQLdb/cursors.py", line 374, in _do_query
db.query(q)
  File "/home/i-06/PycharmProjects/food-front-service/.venv/lib/python3.5/site-packages/MySQLdb/connections.py", line 292, in query
_mysql.connection.query(self, query)

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

I was thinking drop all the data and migrate, but since this app is on service it is impossible. This is migration file I have.

# -*- coding: utf-8 -*-
# Generated by Django 1.11.2 on 2017-11-27 18:52
from __future__ import unicode_literals

from django.db import migrations, models

import uuid

class Migration(migrations.Migration):

    dependencies = [
        ('food', '0112_auto_20171127_1604'),
    ]

    operations = [
    migrations.CreateModel(
        name='FacilityCategory',
        fields=[
            ('id', models.UUIDField(default=uuid.uuid4, primary_key=True, serialize=False)),
            ('name', models.CharField(max_length=20, verbose_name='facility category')),
        ],
        options={
            'verbose_name': 'facility category',
            'verbose_name_plural': 'facility category',
        },
    ),
    migrations.CreateModel(
        name='MiscellaneousCategory',
        fields=[
            ('id', models.UUIDField(default=uuid.uuid4, primary_key=True, serialize=False)),
            ('name', models.CharField(max_length=20, verbose_name='misc category')),
        ],
        options={
            'verbose_name': 'misc category',
            'verbose_name_plural': 'misc category',
        },
    ),
    migrations.RemoveField(
        model_name='product',
        name='facility',
    ),
    migrations.AlterField(
        model_name='product',
        name='free_parking',
        field=models.CharField(blank=True, choices=[('0', '0'), ('1', '1'), ('2', '2'), ('3', '3'), ('4', '4'), ('5', '5'), ('6', '6'), ('7', '7'), ('8', '8'), ('9', '9'), ('10', '10'), ('11', '11'), ('12', '12'), ('13', '13'), ('14', '14'), ('15', '15'), ('16', '16'), ('17', '17'), ('18', '18'), ('19', '19'), ('20', '20'), ('21', '21'), ('22', '22'), ('23', '23'), ('24', '24'), ('25', '25'), ('26', '26'), ('27', '27'), ('28', '28'), ('29', '29'), ('30', '30'), ('31', '31'), ('32', '32'), ('33', '33'), ('34', '34'), ('35', '35'), ('36', '36'), ('37', '37'), ('38', '38'), ('39', '39'), ('40', '40'), ('41', '41'), ('42', '42'), ('43', '43'), ('44', '44'), ('45', '45'), ('46', '46'), ('47', '47'), ('48', '48'), ('49', '49'), ('50', '50'), ('51', '51'), ('52', '52'), ('53', '53'), ('54', '54'), ('55', '55'), ('56', '56'), ('57', '57'), ('58', '58'), ('59', '59'), ('60', '60'), ('61', '61'), ('62', '62'), ('63', '63'), ('64', '64'), ('65', '65'), ('66', '66'), ('67', '67'), ('68', '68'), ('69', '69'), ('70', '70'), ('71', '71'), ('72', '72'), ('73', '73'), ('74', '74'), ('75', '75'), ('76', '76'), ('77', '77'), ('78', '78'), ('79', '79')], max_length=15, verbose_name='free fruit count'),
    ),
    migrations.RemoveField(
        model_name='product',
        name='miscellaneous',
    ),
    migrations.AddField(
        model_name='product',
        name='facility',
        field=models.ManyToManyField(blank=True, choices=[('apple', 'apple'), ('banana', 'banana')], to='food.FacilityCategory', verbose_name='facility '),
    ),
    migrations.AddField(
        model_name='product',
        name='miscellaneous',
        field=models.ManyToManyField(blank=True, choices=[('paper', 'paper'), ('dish', 'dish')], to='food.MiscellaneousCategory', verbose_name='misc'),
    ),
]
migration mysql django python
1 Answers

This is a complete guess so forgive me if I'm barking up the wrong tree (or just completely barking!)

I am surmising that your product model has a stack of data in it already and you're trying to remove two columns (facility and miscellaneous) which are not many to many fields and replacing them with two columns of the same names that are many to many fields, right? That being said I think one issue you may be facing is that when you are creating your new columns, they aren't being populated with a foreign key to your food model. So your backend database is throwing a wobbler! What I would do is either set: 

Null = True

on your new many to many fields in your product model. However if you don't want to allow the column to be Null, then the other option that might work would be to set: 

Default = 1

(or some other appropriate value from your food model). This would allow the migration to set that value on every record of the existing data.

Hope this helps.

user_86222120
Related