Django: "Cannot add foreign key constraint" when creating test tables before running unit tests

Question

I am trying to run unit tests that I've written for my Django project, but testing fails at the database creation stage. I have a single database "test", one of which as a foreign key relationship with another. I run the following:

python3 manage.py test --settings myapp.settings_test

And see the following output:

Creating test database for alias 'test'...
Got an error creating the test database: (1007, "Can't create database 'test_test'; database exists")
Type 'yes' if you would like to try deleting the test database 'test_test', or 'no' to cancel: yes
Destroying old test database for alias 'test'...
Traceback (most recent call last):
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.5/dist-packages/django/db/backends/mysql/base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/cursors.py", line 247, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/cursors.py", line 411, in _query
    rowcount = self._do_query(q)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/cursors.py", line 374, in _do_query
    db.query(q)
  File "/usr/local/lib/python3.5/dist-packages/MySQLdb/connections.py", line 277, in query
    _mysql.connection.query(self, query)
_mysql_exceptions.IntegrityError: (1215, 'Cannot add foreign key constraint')

I've seen many answers such as this: Django MySQL error when creating tables.

Such answers show that, in general, this error is caused by adding a table that has a foreign key field before adding the table that the foreign key referencing. However, I have not found many helpful answers on how to address this in a testing scenario.

Other things I have tried:

I've tried to run tests in a Django shell so that I can then use "django.db.connection" to show the raw MySQL commands Django is running, but I can't seem to figure out how to run the tests in the Django shell.
I've tried to run tests without migrations (https://pypi.org/project/django-test-without-migrations/) but that seems to have no effect.
In my test settings file, I added the MIGRATION_MODULES variable to address the app "badapp" that has the foreign key field:

MIGRATION_MODULES = { 'badapp': None }

but this made no difference as well.

I would like to know:

1.) How can I verify that this error is caused by adding tables in the incorrect order?

2.) How can I force tables to be added in a specific order when running tests?

Tom · Accepted Answer · 2018-04-20 16:19:06Z

0

Based on your reddit version of the question, you have 'init_command': "SET sql_mode='STRICT_TRANS_TABLES'" set in the database options. What happens when you remove that? And what's your default storage engine (I'm assuming InnoDB but want to check)?

answered Apr 20, 2018 at 16:19

Tom

22.9k5 gold badges67 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

adjordan Over a year ago

The default storage engine is indeed InnoDB. When I remove the options parameter from the database definition, nothing changes.

Otrebus · Accepted Answer · 2021-11-04 17:02:03Z

0

This error can occur if you specify a custom MIGRATION_MODULES entry that points to a path that does not contain a __init__.py.

answered Nov 4, 2021 at 17:02

Otrebus

11 silver badge2 bronze badges

Comments

Sebastian Benavente Bravo · Accepted Answer · 2022-01-20 17:04:12Z

0

In my case was a problem with the migrations, i run

python manage.py test --verbosity=3

to check the execution trace, after verifying the trace I could see that the tables of my models were not being created, so i execute

python manage.py makemigrations

and the test can run perfectly It should be noted that my models do not have foreign keys, so the error was quite strange.

answered Jan 20, 2022 at 17:04

Sebastian Benavente Bravo

2513 silver badges4 bronze badges

Collectives™ on Stack Overflow

Django: "Cannot add foreign key constraint" when creating test tables before running unit tests

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related