19

I am using South with my Django app. I have two models that I am changing from having a ForeignKey relation to having a OneToOneField relation. When I ran this migration on my dev database, it ran fine. When the migrations get ran as part of creating a test database, the latest migration fails with a MySQL 1005 error: "Can't create table mydb.#sql-3249_1d (errno: 121)". Doing some Googling revealed that this is usually a problem with trying to add a constraint with the same name as an existing constraint. The specific line in the migration that it fails on is:

The relation was changed from:

class MyModel(models.Model):
    othermodel = models.ForeignKey(OtherModel)

to

class MyModel(models.Model):
    othermodel = models.OneToOneField(OtherModel)

which generated the following statements in the migration:

db.alter_column('myapp_mymodel', 'othermodel_id', self.gf('django.db.models.fields.related.OneToOneField')(to=orm['myapp.OtherModel'], unique=True))

db.create_unique('myapp_mymodel', ['othermodel_id'])

But instead of failing on the create_unique call, it is failing on the alter_column call. I ran the following command to see what SQL was being generated:

python manage.py migrate myapp 0010 --db-dry-run --verbosity=2

and it printed out

myapp:0010_auto__chg_field_mymodel_othermodel__add_unique_mymodel
   = ALTER TABLE `myapp_mymodel` ADD CONSTRAINT `myapp_mymodel_othermodel_id_uniq` UNIQUE (`othermodel_id`) []
   = SET FOREIGN_KEY_CHECKS=1; []
   = ALTER TABLE `myapp_mymodel` ADD CONSTRAINT `myapp_mymodel_othermodel_id_uniq` UNIQUE (`othermodel_id`) []

It seems strange that it is trying to run the ADD CONSTRAINT twice, but if I remove the db.create_unique call, no SQL is generated when I run it with --db-dry-run, but I still get the error if I run it for real.

I am at a loss here, any help is appreciated.

akaihola
  • 26,309
  • 7
  • 59
  • 69
  • 1
    I have created exactly the same migration some days ago and it ran fine. Could you try the same code with a different database backend (I did it on a PostgreSQL database). Also, check your South version. – emyller Sep 13 '11 at 17:37
  • Wish I could help - I made the change, it generated the same python and SQL code, and the migration ran just fine, using mysql 5.1.56 for win32. – Hannele Sep 15 '11 at 21:12
  • Ask this on the South mailing list and you're pretty likely to find the answer. – Malcolm Box Sep 19 '11 at 13:33

3 Answers3

13

You actually don't need a migration at all. OneToOne and ForeignKey relations have a compatible database schema under the hook: a simple column witht the other object ID in one of the table.

Just fake the migration with migrate --fake if you don't want to enter in the trouble of telling south to ignore this change.

Bite code
  • 578,959
  • 113
  • 301
  • 329
  • And if you're at all in doubt that @e-satis is correct about this, take this tidbit from the code (`django.db.models.fields.related`): "A OneToOneField is essentially the same as a ForeignKey, with the exception that always carries a 'unique' constraint with it" And note that OneToOne actually inherits from ForeignKey with only a few minor tweaks. – mlissner Sep 26 '13 at 17:56
  • 1
    To be precise, changing a field from ForeignKey to OneToOneField HAS an effect on the database (at least on those supporting constraints), that shouldn't be skipped: a ForeignKey is not unique, while OneToOneField is. In Django 1.8 - using the native command `makemigrations` on mysql backend - I had no problems at all. The migration correctly deleted the previous non-unique index on the field at the DB level, and set the new unique index. – Augusto Destrero Feb 19 '16 at 11:21
  • Same comment as @AugustoDestrero with Django 3.2 – caram Apr 16 '21 at 16:51
2

First thing I would try would be to add a db.delete_unique(...) in various places to see if I could hack it.

Failing that, I'd split it up into 3 migrations:

  1. a schema migration to add a new column for the OneToOne
  2. a data migration to copy all FK values from old column over to new
  3. a schema migration to remove the old column, which then I'd edit manually to include a command to rename the new column to the same as the old one.
katy lavallee
  • 2,741
  • 1
  • 28
  • 26
1

I agree with @e-satis that the goal here is to fake a migration, but I suggest a different approach if you're working with a team.

If you create a migration then --fake it, all your team members will need to remember to --fake it as well. If any of them don't do this when they upgrade, you're in trouble.

A better approach is to create an empty migration, then migrate it:

manage.py schemamigration yourapp --empty fake_migration_of_foreign_key_to_onetoone
manage.py migrate  # Like you always do!
mlissner
  • 17,359
  • 18
  • 106
  • 169