[Django] #26068: Django 1.8: KEY constraint for parent model missing in m2m [regression]
Django
----------------------------------------------+-----------------------
Reporter: direx | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer (models, ORM) | Version: 1.8
Severity: Normal | Keywords: mysql,m2m
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+-----------------------
I am upgrading from 1.6 to 1.8 and while checking the SQL schema I found
out that the SQL constraints created by Django 1.8 are different.
Apparently there is one KEY constraint missing in m2m relations in Django
1.8 (tested using MySQL).
models.py:
{{{
from django.db import models
class Group(models.Model):
name = models.CharField(max_length=100, db_index=True)
class User(models.Model):
name = models.CharField(max_length=100, db_index=True)
groups = models.ManyToManyField(Group)
}}}
SQL created by Django 1.6 (mysqldump output):
{{{
CREATE TABLE `myapp_user_groups` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`group_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user_id` (`user_id`,`group_id`),
KEY `myapp_user_groups_6340c63c` (`user_id`),
KEY `myapp_user_groups_5f412f9a` (`group_id`),
CONSTRAINT `group_id_refs_id_90b34ba9` FOREIGN KEY (`group_id`)
REFERENCES `myapp_group` (`id`),
CONSTRAINT `user_id_refs_id_ccc79bbf` FOREIGN KEY (`user_id`) REFERENCES
`myapp_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
}}}
SQL created by Django 1.8 (mysqldump output):
{{{
CREATE TABLE `myapp_user_groups` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`group_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user_id` (`user_id`,`group_id`),
KEY `myapp_user_groups_group_id_70cd7f8e20b5a3c3_fk_myapp_group_id`
(`group_id`),
CONSTRAINT
`myapp_user_groups_group_id_70cd7f8e20b5a3c3_fk_myapp_group_id` FOREIGN
KEY (`group_id`) REFERENCES `myapp_group` (`id`),
CONSTRAINT `myapp_user_groups_user_id_dd075ee05371f3c_fk_myapp_user_id`
FOREIGN KEY (`user_id`) REFERENCES `myapp_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
}}}
Note that there is one '''KEY''' constraint missing in 1.8 f(`user_id`).
The interesting thing is that `sqlall` looks the same in both Django
versions:
{{{
BEGIN;
CREATE TABLE `myapp_group` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`name` varchar(100) NOT NULL
)
;
CREATE TABLE `myapp_user_groups` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`user_id` integer NOT NULL,
`group_id` integer NOT NULL,
UNIQUE (`user_id`, `group_id`)
)
;
ALTER TABLE `myapp_user_groups` ADD CONSTRAINT `group_id_refs_id_90b34ba9`
FOREIGN KEY (`group_id`) REFERENCES `myapp_group` (`id`);
CREATE TABLE `myapp_user` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`name` varchar(100) NOT NULL
)
;
ALTER TABLE `myapp_user_groups` ADD CONSTRAINT `user_id_refs_id_ccc79bbf`
FOREIGN KEY (`user_id`) REFERENCES `myapp_user` (`id`);
CREATE INDEX `myapp_group_4da47e07` ON `myapp_group` (`name`);
CREATE INDEX `myapp_user_groups_6340c63c` ON `myapp_user_groups`
(`user_id`);
CREATE INDEX `myapp_user_groups_5f412f9a` ON `myapp_user_groups`
(`group_id`);
CREATE INDEX `myapp_user_4da47e07` ON `myapp_user` (`name`);
COMMIT;
}}}
Using migrations in 1.8 does not change the behavior. It might also apply
to other databases,
--
Ticket URL: <https://code.djangoproject.com/ticket/26068>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: direx | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.8
(models, ORM) | Resolution:
Severity: Normal | worksforme
Keywords: mysql,m2m | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* status: new => closed
* resolution: => worksforme
* needs_tests: => 0
* needs_better_patch: => 0
* needs_docs: => 0
* type: Uncategorized => Bug
Comment:
I couldn't reproduce this. Here's what I see after `manage.py
makemigrations && manage.py migrate`:
{{{
CREATE TABLE `t26068_user_groups` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`group_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user_id` (`user_id`,`group_id`),
KEY `t26068_user_groups_e8701ad4` (`user_id`),
KEY `t26068_user_groups_0e939a4f` (`group_id`),
CONSTRAINT
`t26068_user_groups_group_id_33e3cfed1d09dff9_fk_t26068_group_id` FOREIGN
KEY (`group_id`) REFERENCES `t26068_group` (`id`),
CONSTRAINT
`t26068_user_groups_user_id_308a5058396822ca_fk_t26068_user_id` FOREIGN
KEY (`user_id`) REFERENCES `t26068_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
}}}
I tested without migrations as well. I guess we'll need a sample project
or more details to debug it.
--
Ticket URL: <https://code.djangoproject.com/ticket/26068#comment:1>