7

Today I was tasked with fixing a stack of migrations so they can go fully forwards and backwards from scratch, without relying on starting from a database dump from production. I ran across this.

class Migration(migrations.Migration):

dependencies = [
    migrations.swappable_dependency(settings.AUTH_USER_MODEL),
    ('content', '0038_merge'),
]

operations = [
    migrations.CreateModel(
        name='UserRoles',
        fields=[
            ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
            ('role', models.CharField(max_length=100, choices=[(b'editor', b'Editor Role'), (b'read_only', b'Read Only'), (b'writer', b'Writer Role')])),
            ('site', models.ForeignKey(related_name='site', to='content.Site')),
            ('user', models.ForeignKey(related_name='user_site_roles', to=settings.AUTH_USER_MODEL)),
        ],
    ),
    migrations.AlterUniqueTogether(
        name='userroles',
        unique_together=set([('user', 'site')]),
    ),
]

Forwards and backwards, here is the SQL it generates.

BEGIN;
CREATE TABLE `content_userroles` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `role` varchar(100) NOT NULL, `site_id` integer NOT NULL, `user_id` integer NOT NULL);
ALTER TABLE `content_userroles` ADD CONSTRAINT `content_userroles_user_id_798e435c65731cb9_uniq` UNIQUE (`user_id`, `site_id`);
ALTER TABLE `content_userroles` ADD CONSTRAINT `content_userroles_site_id_3eb32f440311bcb0_fk_sites_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`id`);
ALTER TABLE `content_userroles` ADD CONSTRAINT `content_userroles_user_id_6a54f536e78383a8_fk_auth_user_id` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`);

COMMIT;

BEGIN;
ALTER TABLE `content_userroles` DROP INDEX `content_userroles_user_id_798e435c65731cb9_uniq`;
DROP TABLE `content_userroles` CASCADE;

COMMIT;

When it attempts to drop the unique index, it runs across the error:

Cannot drop index 'content_userroles_user_id_798e435c65731cb9_uniq': needed in a foreign key constraint

Is this a Django bug? Is there a workaround to allow what looks like a simple migration to be reversible? It was suggested that I should just squash the migration stack anyway, but I'm not entirely convinced it would work anyway, as I'm not sure any optimized version of these commands could be generated that doesn't run into this ordering issue. I am open to any level of suggestions no matter how hacky they may seem, as I'll at least learn something from them hopefully. I'm fairly inexperienced with Django but haven't found anything quite like this after searching all afternoon. I have tried putting the unique index creation in a subsequent migration step, so the foreign keys are created, and then the unique index. But that did not change anything as it seems once the unique has been applied to the foreign key fields, it cannot be removed without dropping the whole table.

chief1983
  • 71
  • 4
  • https://stackoverflow.com/questions/8482346/mysql-cannot-drop-index-needed-in-a-foreign-key-constraint may shed more light. TL;DR: you need to drop the FK – Jason Dec 12 '17 at 23:37
  • @Jason I suspected something like that, but what's the best way to go about that within the context of Django migrations? I am worried that something like a hacked in RunSQL or whatever would lead to inconsistency between the schema and models. – chief1983 Dec 13 '17 at 14:59
  • https://stackoverflow.com/questions/38311503/django-1-9-drop-foreign-key-in-migration – Jason Dec 13 '17 at 15:02

0 Answers0