ForeignKey with db_constraint=False doesn't generate an index on MySQL

[Jeroen Pulles]

I have a model that does not want to use a constraint on a foreign key, but still use a database index:

class Category(models.Model):
    text = models.CharField(max_length=3)

class Message(models.Model):
    cat = models.ForeignKey(Category, db_constraint=False)

class IndexMessage(models.Model):
    cat = models.ForeignKey(Category, db_constraint=False, db_index=True)

class StrongMessage(models.Model):
    cat = models.ForeignKey(Category)

The SQLite backend generates an index on the FK column for both models:

$ python manage.py sqlmigrate boohoo 0001_initial
BEGIN;
--
-- Create model Category
--
CREATE TABLE "boohoo_category" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "text" varchar(3) NOT NULL);
--
-- Create model IndexMessage
--
CREATE TABLE "boohoo_indexmessage" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "cat_id" integer NOT NULL);
--
-- Create model Message
--
CREATE TABLE "boohoo_message" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "cat_id" integer NOT NULL);
--
-- Create model StrongMessage
--
CREATE TABLE "boohoo_strongmessage" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "cat_id" integer NOT NULL REFERENCES "boohoo_category" ("id"));
CREATE INDEX "boohoo_indexmessage_05e7bb57" ON "boohoo_indexmessage" ("cat_id");
CREATE INDEX "boohoo_message_05e7bb57" ON "boohoo_message" ("cat_id");
CREATE INDEX "boohoo_strongmessage_05e7bb57" ON "boohoo_strongmessage" ("cat_id");

COMMIT;

With the MySQL backend, this does not create an index on the FK:

$ python manage.py sqlmigrate boohoo 0001_initial
BEGIN;
--
-- Create model Category
--
CREATE TABLE `boohoo_category` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `text` varchar(3) NOT NULL);
--
-- Create model IndexMessage
--
CREATE TABLE `boohoo_indexmessage` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `cat_id` integer NOT NULL);
--
-- Create model Message
--
CREATE TABLE `boohoo_message` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `cat_id` integer NOT NULL);
--
-- Create model StrongMessage
--
CREATE TABLE `boohoo_strongmessage` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `cat_id` integer NOT NULL);
ALTER TABLE `boohoo_strongmessage` ADD CONSTRAINT `boohoo_strongmessage_cat_id_c843b68a_fk_boohoo_category_id` FOREIGN KEY (`cat_id`) REFERENCES `boohoo_category` (`id`);

COMMIT;

I would think that specifying db_constraint=false would only leave out the constraint, and not also the index; Especially with <field>.db_index still set to true. Adding db_index=True does not help, probably because that is the default setting on the FK field object.

This also applies to earlier django versions.

A simple workaround is to use index_together = (('cat', ), ) on the models.

I'm inclined to blame this on django.db.backends.mysql.schema.DatabaseSchemaEditor#_model_indexes_sql,
which may need an extra check for db_constraint being used. This fixes my problem (but changes current django behavior):

--- django/db/backends/mysql/schema.py.orig     2016-02-03 12:01:10.000000000 +0100
+++ django/db/backends/mysql/schema.py  2016-02-03 12:00:19.000000000 +0100
@@ -64,7 +64,7 @@
         )
         if storage == "InnoDB":
             for field in model._meta.local_fields:
-                if field.db_index and not field.unique and field.get_internal_type() == "ForeignKey":
+                if field.db_index and not field.unique and field.get_internal_type() == "ForeignKey" and field.db_constraint:
                     # Temporary setting db_index to False (in memory) to disable
                     # index creation for FKs (index automatically created by MySQL)
                     field.db_index = False

[Walter Doekes]

Interestingly we've run into a bug caused on the same spot.

We run a test case that checks whether there are any unmigrated apps. In the (a) non-DB (SimpleTestCase) and (b) SQLite-DB driven and (c) REUSE_DB test it succeeds, but in the (d) (newly created) InnoDB case, it claims that a lot of foreign key indexes are missing exactly because of this InnoDB exception.

I wonder why that check is there. Removing the db_index=False did not seem to have any negative impact, and it causes the unexpected behaviour to go away.

---

The test in question looks basically like this:

        loader = ModulesMigrationLoader(our_connection, ignore_no_migrations=True)
        
        # Before anything else, see if there's conflicting apps.
        conflicts = loader.detect_conflicts()
        if conflicts:
            raise Exception('Conflicting migrations detected...')
        
        # Set up autodetector.
        autodetector = MigrationAutodetector(
            loader.project_state(),
            ProjectState.from_apps(apps),
            # ^-- the patch impacts this to_state, removing incorrect db_index=False
        )
        
        # Detect changes.
        changed = autodetector.changes(graph=loader.graph)

        self.assertFalse(changed, '...')

[Aaron Elliot Ross]

This code sets db_index = False:

​https://github.com/django/django/blob/master/django/db/backends/mysql/schema.py#L61-L63

I don't think the db/backends/mysql/schema.py code should be modifying the attributes of a Field. I agree that the check needs to take db_constraint into consideration too.

Here's a pull request:

​https://github.com/django/django/pull/6774

All tests pass using MySQL.

[Tim Graham <timograham@…>]

In 6bf79640:

Fixed #26171 -- Made MySQL create an index on ForeignKeys with db_contraint=False.

Refactored "Prevented unneeded index creation on MySQL-InnoDB" (2ceb10f)
to avoid setting db_index=False.

[Tim Graham <timograham@…>]

In 19812868:

[1.10.x] Fixed #26171 -- Made MySQL create an index on ForeignKeys with db_contraint=False.

Refactored "Prevented unneeded index creation on MySQL-InnoDB" (2ceb10f)
to avoid setting db_index=False.

Backport of 6bf7964023487f2a352084e74aca27aecb354d6c from master