Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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.
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;
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
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!
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
                Good question, @campovski, but I've found the solution and posted it above. :) Thanks for your attention.
– Teekin
                Aug 30, 2017 at 14:17
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.