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	Tim Kedmenec		"On MySQL, adding a nullable ForeignKey to an existing table rebuilds the whole table. ADD COLUMN can be ALGORITHM=INSTANT. ADD FOREIGN KEY can't be INSTANT, and it
can't be INPLACE unless foreign_key_checks is off (error 1846: ""Adding foreign keys needs foreign_key_checks=OFF""). Django leaves checks on, so the constraint step is
ALGORITHM=COPY.

Before 3.0, add_field() ran the column add on its own and queued the FK in deferred_sql (code
(https://github.com/django/django/blob/9a0cc54524422dbdd9213e83a8ad7e8a4c13bd3e/django/db/backends/base/schema.py)). The column was instant, but the FK statement was
still a COPY. Since 3.0, 738faf9da2 (https://github.com/django/django/commit/738faf9da2a5cd03148a36375db80746c99c9623) (#30108) puts both in one statement:

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

so the column add is no longer instant either (see comment:11 on #30108). On a ~100GB table this takes hours.

Our workaround is a hand-written migration:

{{{#!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. The test checks that
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