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

Question

A new migration, adding a single, simple table, gives me the error "Cannot add foreign key constraint" during migration.

Here's an existing model, called EventLog:

class EventLog(models.Model):
    """
    The event log.
    """
    user = models.ForeignKey(User, blank=True, null=True)
    timestamp = models.DateTimeField(auto_now=True)
    text = models.TextField(blank=True, null=True)
    ip = models.CharField(max_length=15)
    metadata = JSONField(default={},blank=True)
    product = models.TextField(default=None,blank=True, null=True)
    type = models.ForeignKey(EventType)

    def __unicode__(self):
        return "[%-15s]-[%s] %s (%s)" % (self.type, self.timestamp, self.text, self.user)

    def highlite(self):
        if self.type.highlite:
            return self.type.highlitecss
        return False

Here is then the new model, which I'm trying to create:

class EventLogDetail(models.Model):
    # NOTE: I've already tried switching 'EventLog' out for just EventLog.
    eventlog = models.ForeignKey('EventLog', related_name='details')
    order = models.IntegerField(default=0)
    line = models.CharField(max_length=500)

    class Meta:
        ordering = ['eventlog', 'order']

Seems simple enough, right? So I make the migration:

./manage.py makemigrations:

Migrations for 'accounts':
  accounts/migrations/0016_eventlogdetail.py
    - Create model EventLogDetail

So far, so good. Then, I migrate, like so:

./manage.py migrate:

Operations to perform:
  Apply all migrations: accounts, admin, attention, auth, contenttypes, freedns, hosting, info, mail, sessions, sites, taggit, vserver
Running migrations:
  Applying accounts.0016_eventlogdetail...Traceback (most recent call last):
  File "./manage.py", line 10, in 
    execute_from_command_line(sys.argv)
  File "/home/the_user/code/the_project/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 363, in execute_from_command_line
    utility.execute()
  File "/home/the_user/code/the_project/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 355, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/the_user/code/the_project/local/lib/python2.7/site-packages/django/core/management/base.py", line 283, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/the_user/code/the_project/local/lib/python2.7/site-packages/django/core/management/base.py", line 330, in execute
    output = self.handle(*args, **options)
  File "/home/the_user/code/the_project/local/lib/python2.7/site-packages/django/core/management/commands/migrate.py", line 204, in handle
    fake_initial=fake_initial,
  File "/home/the_user/code/the_project/local/lib/python2.7/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/the_user/code/the_project/local/lib/python2.7/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/the_user/code/the_project/local/lib/python2.7/site-packages/django/db/migrations/executor.py", line 244, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/home/the_user/code/the_project/local/lib/python2.7/site-packages/django/db/backends/base/schema.py", line 93, in __exit__
    self.execute(sql)
  File "/home/the_user/code/the_project/local/lib/python2.7/site-packages/django/db/backends/base/schema.py", line 120, in execute
    cursor.execute(sql, params)
  File "/home/the_user/code/the_project/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/home/the_user/code/the_project/local/lib/python2.7/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/the_user/code/the_project/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/home/the_user/code/the_project/local/lib/python2.7/site-packages/django/db/backends/mysql/base.py", line 101, in execute
    return self.cursor.execute(query, args)
  File "/home/the_user/code/the_project/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/home/the_user/code/the_project/local/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')

Here then is migration itself, in all is Pythonic glory:

# -*- coding: utf-8 -*-
# Generated by Django 1.11.4 on 2017-08-30 12:51
from __future__ import unicode_literals

from django.db import migrations, models
import django.db.models.deletion


class Migration(migrations.Migration):

    dependencies = [
        ('accounts', '0015_product_public'),
    ]

    operations = [
        migrations.CreateModel(
            name='EventLogDetail',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('order', models.IntegerField(default=0)),
                ('line', models.CharField(max_length=500)),
                ('eventlog', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, related_name='details', to='accounts.EventLog')),
            ],
            options={
                'ordering': ['eventlog', 'order'],
            },
        ),
    ]

I've tried renaming the new model and everything in it (event the related_name attribute), in case I were using variable names already taken by some machinery under hood, but with the same results.

Searching online, I've only found one example of this problem with Django specifically (Django MySQL error when creating tables), but that didn't help. There are no migrations to be made to auth, nor can I see why there would be, since we have neither messed with that part nor upgraded any of the packages lately.

Any help deeply appreciated.

Answer 1

I had this error when trying to create a foreign key in common.Activity to point to products.Transaction.

My answer is not original, but I collated some of the answers as they did not specify something very important. utf8 != utf8mb4, and if your tables differ this way, the FK migration will still crash

To find out what character sets have been assigned to the tables

SELECT CCSA.character_set_name 
FROM information_schema.`TABLES` T,information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
AND T.table_schema = "cart_live"
AND T.table_name = "products_transaction";
| latin1             |
+--------------------+
SELECT CCSA.character_set_name 
FROM information_schema.`TABLES` T,information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
AND T.table_schema = "cart_live"
AND T.table_name = "common_activity";
| utf8               |

NOTE: THEY MUST BE EXACTLY THE SAME! latin1 != utf8 != utf8mb4 To see what versions of character sets you have....

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR 
Variable_name LIKE 'collation%';
    +--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_client     | utf8              |
| character_set_connection | utf8              |
| character_set_database   | latin1            |
| character_set_filesystem | binary            |
| character_set_results    | utf8              |
| character_set_server     | latin1            |
| character_set_system     | utf8              |
| collation_connection     | utf8_general_ci   |
| collation_database       | latin1_swedish_ci |
| collation_server         | latin1_swedish_ci |
+--------------------------+-------------------+

and I used utf8_general_ci as follows

ALTER TABLE products_transaction CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
mysql> SELECT CCSA.character_set_name 
    -> FROM information_schema.`TABLES` T,information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
    -> WHERE CCSA.collation_name = T.table_collation
    -> AND T.table_schema = "cart_live"
    -> AND T.table_name = "products_transaction";
| utf8               |

Then running the migration to add the foreign key worked

Answer 2

My issue was that the database in my Django project is created from scratch and tables are imported from mysql dump. The tables in the mysql dump were of CHARSET utf8mb4 while the new tables which I am creating using migration are created with CHARSET latin1. So the new foreignkey is created in a table with latin1 and referring a table with utf8mb4 which throws the error

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

New tables are created with CHARSET latin1 because the default CHARSET of the database which i created was latin1. To check the default CHARSET enter the below command in mysql console.

mysql> SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "DBNAME";

Workaround for this issue is converting the default CHARSET of database to utf8mb4. This is required not only for fixing the integrity error but also Django will have many other problem if the CHARSET is not utf8. To change the CHARSET of the database use this command.

mysql> ALTER DATABASE DBNAME CHARACTER SET utf8 COLLATE utf8_general_ci;

Answer 3

Okay, figured it out.

I tried manually creating the foreign key, which then failed with the same cryptic error message. Searching for solutions focusing entirely on MySQL, I found an answer from @Andrew here: MySQL Cannot Add Foreign Key Constraint, which details the requirements for a foreign key.

One of those requirements is that both tables use the same engine type, which can be either InnoDB or MyISAM. It turns out, that in my database, the older tables are MyISAM and the newer ones are InnoDB, and indeed, this was the root of my problem.

I wrote a short and very messy shell script to fix the problem, which you can see below. Please note that it was neither written with performance nor beauty in mind. Just wanted to get this over with.

#!/bin/bash

DBNAME=excellent_database
PASSWORD=very-very-bad-password-on-many-sides-on-many-sides

# Some of the datetime data in the old MyISAM tables were giving
# InnoDB a rough time so here they are updated to something InnoDB
# feels more comfortable with. Subqueries didn't work and I
# couldn't be bothered to figure out why.
IDS=$(mysql "$DBNAME" -u root -p"$PASSWORD" -e "SELECT id FROM appname_modelname WHERE timestamp_created = '0000-00-00 00:00:00';")
for ROW_ID in $IDS; do
    mysql "$DBNAME" -u root -p"$PASSWORD" -e "UPDATE appname_modelname SET timestamp_created = '0001-01-01 00:00:00' WHERE id = $ROW_ID";
    echo $ROW_ID
done

mysql "$DBNAME" -u root -p"$PASSWORD" -e "SHOW TABLE STATUS WHERE ENGINE = 'MyISAM';" | awk 'NR>1 {print "ALTER TABLE "$1" ENGINE = InnoDB;"}' | mysql -u root -p"$PASSWORD" "$DBNAME"

Hope it helps someone else!

Answer 4

Try removing quote:

eventlog = models.ForeignKey(EventLog, related_name='details')

or if you want to use quote then use app_name also

eventlog = models.ForeignKey('accounts.EventLog', related_name='details')

Answer 5

Change

eventlog = models.ForeignKey('EventLog', related_name='details')

to

eventlog = models.ForeignKey(EventLog, related_name='details')

;)