id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
36355	sqlmigrate gives unexpected output for an AlterField on a PK when another app has a model with a FK	Tim Bell	JaeHyuckSa	"I've recently discovered a scenario where the output of `sqlmigrate` for a particular migration is significantly different from the actual SQL run when the migration is applied. (I am following the advice [https://docs.djangoproject.com/en/5.2/ref/django-admin/#django-admin-sqlmigrate here] to run `sqlmigrate` ""against a copy of the database you wish to later apply it on"".) This occurs when a primary key field is being altered when there is a foreign key field on a model in another app that refers to that primary key field.

Consider an app ""myapp"" with this model:

{{{
class MyModel(models.Model):
    id = models.AutoField(primary_key=True)
}}}

And another app ""otherapp"" with this model:

{{{
from myapp.models import MyModel

class OtherModelInOtherApp(models.Model):
    my_model = models.ForeignKey(MyModel, on_delete=models.CASCADE)
}}}

Run `makemigrations` and `migrate` to apply those initial migrations. (A sample repo containing this scenario is [https://github.com/timb07/sqlmigrate_bug here]. The [https://github.com/timb07/sqlmigrate_bug/commit/e3002b11d02c7526408d2cceb174f60d66a54eb3 initial commit] represents this state.)

Then change the `id` field of `MyModel` to `BigAutoField`, and run `makemigrations` again. ([https://github.com/timb07/sqlmigrate_bug/commit/d9106ad1b691392fc6f12aa2d19c5bce8f059910 This commit] represents this state.)

Now run `sqlmigrate myapp 0002`, giving this output (using Postgresql):

{{{
BEGIN;
--
-- Alter field id on mymodel
--
ALTER TABLE ""myapp_mymodel"" ALTER COLUMN ""id"" TYPE bigint USING ""id""::bigint;
ALTER SEQUENCE IF EXISTS ""myapp_mymodel_id_seq"" AS bigint;
COMMIT;
}}}

There is no mention of the `otherapp_othermodelinotherapp`, its column `my_model_id`, or the foreign key constraint referencing `myapp_mymodel.id`. In contrast, when the migration is applied, the existing FK constraint is dropped, the `my_model_id` is altered to bigint, and a new FK constraint is created.

My understanding is that the because there is no dependency from the `myapp 0002_alter_mymodel_id` migration on the `otherapp 0001_initial` migration, `sqlmigrate` does not consider the latter migration when determining the state of the models. That means that it doesn't know that there is a foreign key relationship that will be affected by the `AlterField` migration operation.

Indeed, adding a dependency in `otherapp/migrations/0001_initial.py` like this:

{{{
    run_before = [
        ('myapp', '0002_alter_mymodel_id'),
    ]
}}}

results in the output of `sqlmigrate` being as expected now:

{{{
BEGIN;
--
-- Alter field id on mymodel
--
SET CONSTRAINTS ""otherapp_othermodeli_my_model_id_cc392f6b_fk_myapp_mym"" IMMEDIATE; ALTER TABLE ""otherapp_othermodelinotherapp"" DROP CONSTRAINT ""otherapp_othermodeli_my_model_id_cc392f6b_fk_myapp_mym"";
ALTER TABLE ""myapp_mymodel"" ALTER COLUMN ""id"" TYPE bigint USING ""id""::bigint;
ALTER SEQUENCE IF EXISTS ""myapp_mymodel_id_seq"" AS bigint;
ALTER TABLE ""otherapp_othermodelinotherapp"" ALTER COLUMN ""my_model_id"" TYPE bigint USING ""my_model_id""::bigint;
ALTER TABLE ""otherapp_othermodelinotherapp"" ADD CONSTRAINT ""otherapp_othermodelinotherapp_my_model_id_cc392f6b_fk"" FOREIGN KEY (""my_model_id"") REFERENCES ""myapp_mymodel"" (""id"") DEFERRABLE INITIALLY DEFERRED;
COMMIT;
}}}

This behaviour has been observed in various Django 5.x and 4.x versions; I suspect it has always been present since migrations were added to Django.

It would be good if this behaviour was noted in the documentation for `sqlmigrate`. Ideally though, `sqlmigrate` should reflect the current state of applied migrations in the database, as it already does to resolve constraint names."	Bug	assigned	Migrations	5.2	Normal			Tim Bell Lily Foote	Accepted	1	0	0	0	0	0