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
37425	Adding a nullable ForeignKey on MySQL rebuilds the table (regression from #30108)	Tim Kedmenec		"Since 3.0, add_field() puts the FK constraint in the same statement as the column:

{{{#!sql
ALTER TABLE t
    ADD COLUMN owner_id integer NULL,
    ADD CONSTRAINT t_owner_id_fk FOREIGN KEY (owner_id) REFERENCES owner (id);
}}}

On MySQL, ADD COLUMN can be ALGORITHM=INSTANT, but adding a foreign key can't (error 1846 with foreign_key_checks on). MySQL picks one algorithm per statement, so the
combined statement is a full table rebuild. Simon pointed this out in comment:11 of #30108, but I couldn't find a follow-up ticket. On a ~100GB table, adding a
nullable FK takes hours.

Our workaround is a hand-written migration that splits the two steps:

{{{#!python
migrations.SeparateDatabaseAndState(
    state_operations=[
        migrations.AddField(
            model_name=""book"",
            name=""owner"",
            field=models.ForeignKey(
                ""app.Owner"",
                null=True,
                on_delete=models.SET_NULL,
            ),
        ),
    ],
    database_operations=[
        migrations.RunSQL(
            ""ALTER TABLE app_book ""
            ""ADD COLUMN owner_id integer NULL, ALGORITHM=INSTANT"",
            reverse_sql=(
                ""ALTER TABLE app_book DROP COLUMN owner_id, ALGORITHM=INSTANT""
            ),
        ),
        migrations.RunSQL(""SET foreign_key_checks = 0""),
        migrations.RunSQL(
            ""ALTER TABLE app_book ""
            ""ADD CONSTRAINT app_book_owner_fk ""
            ""FOREIGN KEY (owner_id) REFERENCES app_owner (id), ""
            ""ALGORITHM=INPLACE, LOCK=NONE""
        ),
        migrations.RunSQL(""SET foreign_key_checks = 1""),
    ],
)
}}}

For a nullable FK, all existing rows are NULL, so there's nothing to validate and it's safe to disable foreign_key_checks for the constraint step.

The patch makes the MySQL schema editor do this for nullable FKs with db_constraint=True. Non-null FKs keep the single statement. It includes a test that checks
information_schema.innodb_tables.total_row_versions goes up by one, which is how an instant add shows up. A rebuild resets it to 0.

PR: https://github.com/django/django/pull/22146"	Bug	new	Migrations	6.1	Normal		mysql, instant, foreign key		Unreviewed	0	0	0	0	0	0