Django执行makemigrations操作时出现“无法添加外键约束”错误

Question

我用的是Django 1.9.5和python 2.7。我也在用MySQL做数据库。当我尝试进行迁移时，我得到了以下错误

"无法添加外键约束"

当我尝试将许多字段添加到现有表中时，我收到此错误。当我进行迁移和迁移时，会创建多对多关系表，但没有外键约束。

在运行服务器时，它还表示：；

"您有未应用的迁移；在应用它们之前，您的应用可能无法正常工作。"

运行pythonmanage.py迁移来应用它们。我可以通过网站向这个表添加新数据，但是没有外键约束。但是我需要为关系创建带有外键约束的表字段。该操作将在Worker和Worker的表之间创建多对多的关系。MySQL是InnoDB配置的。

错误日志：

        # python manage.py migrate
    Operations to perform:
      Apply all migrations: authtoken, sessions, admin, auth, reversion, contenttypes, company
    Running migrations:
      Rendering model states... DONE
      Applying company.0005_Worker_duty_type...Traceback (most recent call last):
      File "manage.py", line 10, in <module>
        execute_from_command_line(sys.argv)
      File "/home/dataproj/Env/dataproj/lib/python2.7/site-packages/django/core/management/__init__.py", line 353, in execute_from_command_line
        utility.execute()
      File "/home/dataproj/Env/dataproj/lib/python2.7/site-packages/django/core/management/__init__.py", line 345, in execute
        self.fetch_command(subcommand).run_from_argv(self.argv)
      File "/home/dataproj/Env/dataproj/lib/python2.7/site-packages/django/core/management/base.py", line 348, in run_from_argv
        self.execute(*args, **cmd_options)
      File "/home/dataproj/Env/dataproj/lib/python2.7/site-packages/django/core/management/base.py", line 399, in execute
        output = self.handle(*args, **options)
      File "/home/dataproj/Env/dataproj/lib/python2.7/site-packages/django/core/management/commands/migrate.py", line 200, in handle
        executor.migrate(targets, plan, fake=fake, fake_initial=fake_initial)
      File "/home/dataproj/Env/dataproj/lib/python2.7/site-packages/django/db/migrations/executor.py", line 92, in migrate
        self._migrate_all_forwards(plan, full_plan, fake=fake, fake_initial=fake_initial)
      File "/home/dataproj/Env/dataproj/lib/python2.7/site-packages/django/db/migrations/executor.py", line 121, in _migrate_all_forwards
        state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
      File "/home/dataproj/Env/dataproj/lib/python2.7/site-packages/django/db/migrations/executor.py", line 198, in apply_migration
        state = migration.apply(state, schema_editor)
      File "/home/dataproj/Env/dataproj/lib/python2.7/site-packages/django/db/backends/base/schema.py", line 90, in __exit__
        self.execute(sql)
      File "/home/dataproj/Env/dataproj/lib/python2.7/site-packages/django/db/backends/base/schema.py", line 110, in execute
        cursor.execute(sql, params)
      File "/home/dataproj/Env/dataproj/lib/python2.7/site-packages/django/db/backends/utils.py", line 79, in execute
        return super(CursorDebugWrapper, self).execute(sql, params)
      File "/home/dataproj/Env/dataproj/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
        return self.cursor.execute(sql, params)
      File "/home/dataproj/Env/dataproj/lib/python2.7/site-packages/django/db/utils.py", line 95, in __exit__
        six.reraise(dj_exc_type, dj_exc_value, traceback)
      File "/home/dataproj/Env/dataproj/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
        return self.cursor.execute(sql, params)
      File "/home/dataproj/Env/dataproj/lib/python2.7/site-packages/django/db/backends/mysql/base.py", line 112, in execute
        return self.cursor.execute(query, args)
      File "/home/dataproj/Env/dataproj/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
        self.errorhandler(self, exc, value)
      File "/home/dataproj/Env/dataproj/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
        raise errorclass, errorvalue
    django.db.utils.IntegrityError: (1215, 'Cannot add foreign key constraint')

代码如下:

models.py :


class Workerduty(BaseModel):

    name = models.CharField(max_length=50, unique=True, verbose_name=_("Worker Duty"))

    def __unicode__(self):
        return self.name

    class Meta:
        ordering = ['name']

class Worker(BaseModel):

    duty_type = models.ManyToManyField(Workerduty, verbose_name=_("Worker Duty"))
    name = models.CharField(max_length=100, db_index=True, verbose_name=_("Name"))

    class Meta:
        permissions = (
            ("Workerl", _("Workerl")),
        )
        ordering = ['name']

    def __unicode__(self):
        return "%s %s" % (self.name)

    def save(self, *args, **kwargs):
        self.name = self.name.title()
        grps = []
        for Workerduty_obj in self.Workerduty.all():
            grp = Group.objects.get_or_create(name=Workerduty_obj.name)[0]
            grps.append(grp)
        self.user.grps = grps
        self.user.save()
        super(Worker, self).save(*args, **kwargs)

迁移文件 ：

    # -*- coding: utf-8 -*-
# Generated by Django 1.9.5 on 2018-06-28 20:09
from __future__ import unicode_literals

from django.db import migrations, models
import uuid


class Migration(migrations.Migration):

    dependencies = [
        ('company', '0001_initial'),
    ]

    operations = [
        migrations.CreateModel(
            name='Workerduty',
            fields=[
                ('uuid', models.UUIDField(default=uuid.uuid4, primary_key=True, serialize=False, verbose_name='Unique Identifier')),
                ('name', models.CharField(max_length=50, unique=True, verbose_name='Worker Duty')),
            ],
            options={
                'ordering': ['name'],
            },
        ),
        migrations.AddField(
            model_name='worker',
            name='worker_type',
            field=models.ManyToManyField(to='college.Workerduty', verbose_name='Worker Duty'),
        ),
    ]

Answer 1

解决方案：我正在处理一个现有的项目。MySQL数据库是UTF8排序的。我用新的MySQL准备了一个测试环境，并克隆了数据库。但是默认的mysql排序规则是latin1，而导入的DB是UTF8。所以旧表和django新创建的表之间存在冲突。所以我将新的mysql安装的排序规则更改为utf8。所以新创建的表现在使用新的默认utf8排序规则创建，这与导入的DB相同。

我在mysql控制台上使用了以下命令：

SELECT DISTINCT concat('ALTER DATABASE `', TABLE_SCHEMA, '` CHARACTER SET utf8 COLLATE utf8_general_ci;') from information_schema.tables where TABLE_SCHEMA like  'DbName';