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:

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:

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