CheckConstraints with F objects don't apply on SQLite

[Adam Johnson]

Take this model with CheckConstraint:

class Book(models.Model):
    percent_read = models.PositiveIntegerField()
    percent_unread = models.PositiveIntegerField()
    percent_ignored = models.PositiveIntegerField()

    def __str__(self):
        return f"{self.id} - {self.percent_read}% read"

    class Meta:
        constraints = [
            models.CheckConstraint(
                check=models.Q(
                    percent_read=(
                        100
                        - models.F("percent_unread")
                        - models.F("percent_ignored")
                    )
                ),
                name="percentages_sum_100",
            )
        ]

Applying the migration to create the constraint works on MariaDB but fails on SQLite.

The sqlmigrate output on SQLite shows the F objects get resolved to the full table name, which is what breaks during renaming phase in the migration:

$ python manage.py sqlmigrate core 0002
BEGIN;
--
-- Create constraint percentages_sum_100 on model book
--
CREATE TABLE "new__core_book" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "percent_read" integer unsigned NOT NULL CHECK ("percent_read" >= 0), "percent_unread" integer unsigned NOT NULL CHECK ("percent_unread" >= 0), "percent_ignored" integer unsigned NOT NULL CHECK ("percent_ignored" >= 0), CONSTRAINT "percentages_sum_100" CHECK ("percent_read" = ((100 - "new__core_book"."percent_unread") - "new__core_book"."percent_ignored")));
INSERT INTO "new__core_book" ("id", "percent_read", "percent_unread", "percent_ignored") SELECT "id", "percent_read", "percent_unread", "percent_ignored" FROM "core_book";
DROP TABLE "core_book";
ALTER TABLE "new__core_book" RENAME TO "core_book";
COMMIT;
$ python manage.py migrate
...
django.db.utils.DatabaseError: malformed database schema (core_book) - no such column: new__core_book.percent_unread

Full repo for reproduction: ​https://github.com/adamchainz/django-issue-check-constraint-reference

[Adam Johnson]

Duplicate of #31197, apologies I got a couple 504's from Nginx so resubmitted.