AlterField drops contraints in the wrong order when performed as reverse migration

[Matthias Dellweg]

The following migration step

migrations.AlterField(
            model_name='filedistribution',
            name='distribution_ptr',
            field=models.OneToOneField(auto_created=True,
                                       on_delete=django.db.models.deletion.CASCADE,
                                       parent_link=True, primary_key=True,
                                       related_name='file_filedistribution', serialize=False,
                                       to='core.Distribution'),
            preserve_default=False,
        ),

in reverse mode produces

ALTER TABLE "file_filedistribution" ALTER COLUMN "distribution_ptr_id" DROP NOT NULL;
ALTER TABLE "file_filedistribution" DROP CONSTRAINT "file_filedistribution_distribution_ptr_id_5ce5564b_pk";

leading to an error

django.db.utils.ProgrammingError: column "distribution_ptr_id" is in a primary key

Switching the order of the statements enabled me execute the SQL just fine.

[Mariusz Felisiak]

Thanks for this report. Can you share a database state before this operation? or a sample project which reproduces this issue.

Also, Django 2.2 is in extended support so it receives only security fixes. Can you reproduce this issue on the main branch?

[Matthias Dellweg]

I could reproduce with Django==3.2.

I created a sample project here:
​https://github.com/mdellweg/migrationtest

If you configure this app to a postgres driver and call migrationtest/manage.py sqlmigrate --backwards pk_migration 0002, you will see:

BEGIN;
--
-- Alter field secondbase_ptr on child
--
SET CONSTRAINTS "pk_migration_child_secondbase_ptr_id_9b785ce8_fk_pk_migrat" IMMEDIATE; ALTER TABLE "pk_migration_child" DROP CONSTRAINT "pk_migration_child_secondbase_ptr_id_9b785ce8_fk_pk_migrat";
ALTER TABLE "pk_migration_child" ALTER COLUMN "secondbase_ptr_id" DROP NOT NULL;
ALTER TABLE "pk_migration_child" DROP CONSTRAINT "pk_migration_child_secondbase_ptr_id_9b785ce8_pk";
ALTER TABLE "pk_migration_child" ADD CONSTRAINT "pk_migration_child_secondbase_ptr_id_9b785ce8_uniq" UNIQUE ("secondbase_ptr_id");
ALTER TABLE "pk_migration_child" ADD CONSTRAINT "pk_migration_child_secondbase_ptr_id_9b785ce8_fk_pk_migrat" FOREIGN KEY ("secondbase_ptr_id") REFERENCES "pk_migration_secondbase" ("id") DEFERRABLE INITIALLY DEFERRED;
--
-- Remove field firstbase_ptr from child
--
ALTER TABLE "pk_migration_child" ADD COLUMN "firstbase_ptr_id" bigint NOT NULL PRIMARY KEY CONSTRAINT "pk_migration_child_firstbase_ptr_id_3ea33ffb_fk_pk_migrat" REFERENCES "pk_migration_firstbase"("id") DEFERRABLE INITIALLY DEFERRED; SET CONSTRAINTS "pk_migration_child_firstbase_ptr_id_3ea33ffb_fk_pk_migrat" IMMEDIATE;
--
-- MIGRATION NOW PERFORMS OPERATION THAT CANNOT BE WRITTEN AS SQL:
-- Raw Python operation
--
--
-- Add field secondbase_ptr to child
--
SET CONSTRAINTS "pk_migration_child_secondbase_ptr_id_9b785ce8_fk_pk_migrat" IMMEDIATE; ALTER TABLE "pk_migration_child" DROP CONSTRAINT "pk_migration_child_secondbase_ptr_id_9b785ce8_fk_pk_migrat";
ALTER TABLE "pk_migration_child" DROP COLUMN "secondbase_ptr_id" CASCADE;
COMMIT;

Which will fail to migrate with column "secondbase_ptr_id" is in a primary key when trying to execute ALTER TABLE "pk_migration_child" ALTER COLUMN "secondbase_ptr_id" DROP NOT NULL;.

If you move ALTER TABLE "pk_migration_child" DROP CONSTRAINT "pk_migration_child_secondbase_ptr_id_9b785ce8_pk"; one place up in the command chain, the migration will succeed.

[Mariusz Felisiak]

Matthias, thanks for details, however this is not a migration created by Django. Everything works when I will remove 0002_auto_20210413_0915.py and use a generated migration:

$ python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, pk_migration, sessions
Running migrations:
  Applying pk_migration.0001_initial... OK
  Applying pk_migration.0002_auto_20210413_1116... OK

$ python manage.py migrate pk_migration zero
Operations to perform:
  Unapply all migrations: pk_migration
Running migrations:
  Rendering model states... DONE
  Unapplying pk_migration.0002_auto_20210413_1116... OK
  Unapplying pk_migration.0001_initial... OK

You could probably make it works with SeparateDatabaseAndState().

[Matthias Dellweg]

Thanks for you answer, Mariusz.

I do not think that this solves the problem. Yes the migration was handcrafted (well heavily modified), but autogenerating it was impossible without providing a default value for what is supposed to be the new primary key of the model.
So with actual ojects of that Child model, what you need to do is:

1. Add the new secondbase_ptr (as null and not and index)
2. Migrate all associated objects from FirstBase to SecondBase and make secondbase_ptr reference them (This is where RunPython comes to the play)
3. Remove the firstbase_ptr (to make place for another primary key)
4. Alter secondbase_ptr to not-null and being the primary key.

My best way to resolve this results in the question: Can i call AlterField in a way to turn the primary key into a common field? Or is there another way to pivot the primary key?