Opened 9 years ago

Closed 9 years ago

#26068 closed Bug (worksforme)

Django 1.8: KEY constraint for parent model missing in m2m [regression]

Reported by: direx Owned by: nobody
Component: Database layer (models, ORM) Version: 1.8
Severity: Normal Keywords: mysql, m2m
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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,

Change History (1)

comment:1 by Tim Graham, 9 years ago

Resolution: worksforme
Status: newclosed
Type: UncategorizedBug

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.

Note: See TracTickets for help on using tickets.
Back to Top