Log in

Foreign key constraint is incorrectly formed

schizoidDavid Curran
  • 11 Feb

Hi,

I installed Spirit from Git and placed it into my existing project. When I run python manage.py migrate I get the error:

Can\'t create table database.#sql-e53_aca (errno: 150 "Foreign key constraint is incorrectly formed")

It appears to be caused by the foreign key in CommentLike

class CommentLike(models.Model):

user = models.ForeignKey(settings.AUTH_USER_MODEL, related_name='st_comment_likes')

I don't see anything wrong with this though. Any idea what the cause would be?

I'm running Django 1.10

Most likely I've missed something very important

nitelyEsteban Castro Borsani
  • 5
  • 11 Feb

What database is this (sqlite, postgresql...)? Do you have a custom user model?

Also, provide the full command output (including command, traceback, etc) and the settings.py content.

If it's an empty or local database you may try removing it and migrating everything from scratch.

schizoidDavid Curran
  • 1
  • 12 Feb

mariadb, so mysql. No custom user model, just the base Django one.

I dropped the database and recreated it, got the below error on running manage.py

python manage.py migrate

Everything went ok until:

Applying spirit_topic_poll.0003_auto_20161114_1452...Traceback (most recent call last):
File "/usr/local/lib/python3.5/dist-packages/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
File "/usr/local/lib/python3.5/dist-packages/django/db/backends/mysql/base.py", line 112, 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 42, 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 270, in query
_mysql.connection.query(self, query)
_mysql_exceptions.OperationalError: (1829, "Cannot drop column 'topic_id': needed in a foreign key constraint 'spirit__poll_id_15a514de_fk_spirit_topic_poll_topicpoll_topic_id' of table 'cricket.spirit_topic_poll_topicpollchoice'")

schizoidDavid Curran
  • 12 Feb

I commented out spirit.polls in my settings.py and the migration went well.

Had to recreate my users but that's not a problem.

nitelyEsteban Castro Borsani
  • 1
  • 12 Feb

spirit.topic.poll is no longer used so it's safe to remove it. However, It seems the migration is removing the models in the wrong order . I'll look into it, thanks for reporting it.

Weird, I can't reproduce this with postgresql.

schizoidDavid Curran
  • 12 Feb

Yeah I saw the comment so removed it. I'll show what order they're in in my settings.py:

INSTALLED_APPS = [
'django.contrib.admin',
'django.contrib.auth',
'django.contrib.contenttypes',
'django.contrib.sessions',
'django.contrib.messages',
'django.contrib.staticfiles',
'web.apps.WebConfig',

# forum APPS
'spirit.core',
'spirit.admin',
'spirit.search',
'spirit.user',
'spirit.user.admin',
'spirit.user.auth',
'spirit.category',
'spirit.category.admin',
'spirit.topic',
'spirit.topic.admin',
'spirit.topic.favorite',
'spirit.topic.moderate',
'spirit.topic.notification',

'spirit.topic.poll',

'spirit.topic.private',
'spirit.topic.unread',
'spirit.comment',
'spirit.comment.bookmark',
'spirit.comment.flag',
'spirit.comment.flag.admin',
'spirit.comment.history',
'spirit.comment.like',
'spirit.comment.poll',
'djconfig',
'haystack',]

C&P from the spirit settings.py

nitelyEsteban Castro Borsani
  • 12 Feb

Seems fine. The issue is likely related to removing all the poll models at once. SQLite and postgresql seem to not care about the order Django tell them to remove the models/tables, but mysql does.

Reply