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
26171	ForeignKey with db_constraint=False doesn't generate an index on MySQL	Jeroen Pulles	Aaron Elliot Ross	"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
}}}"	Bug	closed	Migrations	1.9	Normal	fixed	mysql		Accepted	1	0	0	0	0	0