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:

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:

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