Migration fails because of duplicate constraint name when renaming and re-creating a model

[Remiremi]

I have reached a point where django migration fails because it tries to create a constraint with a name that is already used by another constraint. I am using MySQL and tried with both PyMySQL connector and Oracle's mysql-connector-python. I might be wrong but I suspect the same would happen with other connectors or with PostgreSQL. I have tried with Django 1.7.6 and 1.7.7.

1. Create model ModelB with a FK on ModelA

class ModelA(models.Model):
        pass

class ModelB(models.Model):
        a = models.ForeignKey(ModelA)

2. Run manage.py makemigrations.

3. Rename ModelB to ModelC

class ModelA(models.Model):
        pass

class ModelC(models.Model):
        a = models.ForeignKey(ModelA)

4. Run manage.py makemigrations. When asked whether you renamed ModelB to ModelC, answer yes.

5. Add back Model B

class ModelA(models.Model):
        pass

class ModelC(models.Model):
        a = models.ForeignKey(ModelA)

class ModelB(models.Model):
        a = models.ForeignKey(ModelA)

6. Run manage.py makemigrations

7. Run manage.py migrate

This last step fails with error

django.db.utils.InternalError: (1022, u"Can't write; duplicate key in table '#sql-37bd_127'")

The django.db log shows that the SQL query causing the failure is:

[24/Mar/2015 11:11:46] DEBUG [django.db.backends.schema:94] ALTER TABLE `myapp_modelb` ADD CONSTRAINT `myapp_modelb_a_id_73cac46a7c85ae49_fk_myapp_modela_id` FOREIGN KEY (`modela_id`) REFERENCES `myapp_modela` (`id`); (params [])

After inspection of the database, ModelC does have a constraint with that name.

It's probably possible to go around this issue by renaming ModelC'S constraint, but the same failure also happens when building the test database by running manage.py test. I do not have a workaround for that case.

Full output of the migration is attached.

[Baptiste Mispelon]

I can confirm that the same thing happens with postgres:

Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "django/core/management/__init__.py", line 330, in execute_from_command_line
    utility.execute()
  File "django/core/management/__init__.py", line 322, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "django/core/management/base.py", line 347, in run_from_argv
    self.execute(*args, **cmd_options)
  File "django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "django/core/management/commands/migrate.py", line 195, in handle
    executor.migrate(targets, plan, fake=fake, fake_initial=fake_initial)
  File "django/db/migrations/executor.py", line 94, in migrate
    self.apply_migration(states[migration], migration, fake=fake, fake_initial=fake_initial)
  File "django/db/migrations/executor.py", line 131, in apply_migration
    state = migration.apply(state, schema_editor)
  File "django/db/backends/base/schema.py", line 87, in __exit__
    self.execute(sql)
  File "django/db/backends/base/schema.py", line 107, in execute
    cursor.execute(sql, params)
  File "django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "django/db/utils.py", line 95, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "django/utils/six.py", line 658, in reraise
    raise value.with_traceback(tb)
  File "django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: relation "bug24528_modelb_e2b453f4" already exists

[Markus Holtermann]

Just to get that straight: before migrating myapp.0003, ModelC still has a constraint, right? And while migrating myapp.0003 the table for ModelB is created and when adding the constraint to ModelB's table, migrate blows up?

[Remiremi]

@MarkusH: exactly :)

[Simon Charette]

Duplicate of #23577.