Forums

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

I have a big model with a lot of fields, everything working on it, then I have just added a new field, ManyToMany to a existing table. It worked very well when I made it on my local database, but when I tried to run the migration on python anywhere I got the following error django.db.utils.IntegrityError: (1215, 'Cannot add foreign key constraint'). Here is the full stack trace.

Applying ath.0038_appointment_cid_many...Traceback (most recent call last):
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.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 15, in <module>
    execute_from_command_line(sys.argv)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/django/core/management/__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/django/core/management/__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/django/core/management/base.py", line 316, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/django/core/management/base.py", line 353, in execute
    output = self.handle(*args, **options)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/django/core/management/base.py", line 83, in wrapped
    res = handle_func(*args, **kwargs)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/django/core/management/commands/migrate.py", line 203, in handle
    fake_initial=fake_initial,
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/django/db/migrations/executor.py", line 244, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 106, in __exit__
    self.execute(sql)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 133, in execute
    cursor.execute(sql, params)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/home/hugodepaula/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
django.db.utils.IntegrityError: (1215, 'Cannot add foreign key constraint')

I have read a few posts on stackoverflow about Django error with ENGINE=MyISAM and ENGINE=InnoDB, but it's not looking like the case here, I checked both tables and both tables are using InnoDB. I have checked and the migration starts, create the new table for the relation ManyToMany but it doesn't finish the migration and throw this error, when I checked the app migration with manage.py showmigrations appname it shows that the last one is not checked. Any help would be appreciated XD.

That's a pretty odd one. Can we take a look at your files? We can see them from our admin interface, but we always ask for permission first.

Yes, please!

There does not seem to be a 0038 migration, so I can't really tell what the issue might be. Are you running the same version of Django locally and on PythonAnywhere?

I removed it from the folder because the migration didn't worked. I just added the file again 0038_appointment. Also I have changed my model.py back to what it was before the migration. If it helps, the line I have added on the model file was cid_many = models.ManyToManyField(Cid10, blank=True, null=True).

OK, thanks! All of the code certainly looks OK, as does the migration. I think it might be useful to find out what the SQL it's trying to run for the migration is. Could you run this command and let us know what SQL code it outputs?

python manage.py sqlmigrate ath 0038

Sure, here is the SQL:

BEGIN;
--
-- Add field cid_many to appointment
--
CREATE TABLE `ath_appointment_cid_many` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `appointment_id` integer NOT NULL, `cid10_id` varchar(255
) NOT NULL);
ALTER TABLE `ath_appointment_cid_many` ADD CONSTRAINT `ath_appointment_cid__appointment_id_3ca8c05b_fk_ath_appoi` FOREIGN KEY (`appointment_id`) REF
ERENCES `ath_appointment` (`id`);
ALTER TABLE `ath_appointment_cid_many` ADD CONSTRAINT `ath_appointment_cid_many_cid10_id_9056fc74_fk_ath_cid10_id` FOREIGN KEY (`cid10_id`) REFERENC
ES `ath_cid10` (`id`);
ALTER TABLE `ath_appointment_cid_many` ADD CONSTRAINT ath_appointment_cid_many_appointment_id_cid10_id_ec40ed93_uniq UNIQUE (`appointment_id`, `cid1
0_id`);
COMMIT;

Thanks! So, having looked at the DB, it appears that it's successfully created the ath_appointment_cid_many table, as you mentioned earlier, and it has also created the first constraint, ath_appointment_cid__appointment_id_3ca8c05b_fk_ath_appoi.

However, the ath_appointment_cid_many_cid10_id_9056fc74_fk_ath_cid10_id constraint does not exist, so presumably it's the creation of that constraint that is failing -- the one that cretes the foreign key to ath_cid10.

Looking at the tables in your DB, I noticed one oddity. ath_appointment_cid_many has DEFAULT CHARSET=utf8, but ath_cid10 has DEFAULT CHARSET=latin1. I created a test database inside my own account and tried to create a foreign key constraint from a VARCHAR(255) in a utf8 table to one in a latin1 table, and got an error -- a different one to the one you got, but my account is on MySQL 5.5, and yours is on 5.6. Changing the charsets on the latin1 table to utf8 fixed the problem -- once I had done it, I could create the constraint. So I'm reasonably positive that this might be the cause of the problem in this case.

It looks like a lot of tables in your database have various latin1-derived charsets, but the default charset for new tables is utf8.

Which charset is the one you want to be using for your DB? If it's the latin1-based one, then you can use something like

ALTER DATABASE <databasename> CHARACTER SET something COLLATE something_general_ci;

...replacing the something and the something_general_ci appropriately, and then add the appropriate code to your database connection settings as per this Stack Overflow answer.

Then I think the best way to get your database back to where it should be would be to drop the ath_appointment_cid_many table, and then re-introduce the code and the migration, and then to migrate.

If you want to move everything over to utf8, the steps will be different, of course -- just let us know if that's the case.

It worked, just like you said. Thank you very much!

laughs in Flask