#26171: Where's the index on a foreign key with db_constraint=false, when using
MySQL?
-------------------------------+--------------------
Reporter: jeroenp | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 1.9
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+--------------------
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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26171>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/050.2f5d2aa9c456dbe1e79e5f381b189625%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
