Opened 2 years ago

Closed 2 years ago

#23279 closed Bug (wontfix)

REFERENCES constraints are not set to the fileds in an intermediate table created by syncdb for SQLite3

Reported by: Annadel Owned by: nobody
Component: Uncategorized Version: 1.6
Severity: Normal Keywords: syncdb manytomany
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Baptiste Mispelon)

If I have the following class definition:

class Foo(models.Model):
    bars = models.ManyToManyField('Bar')
    class Meta:
        db_table = 'foo'

class Bar(models.Model):
    class Meta:
        db_table = 'bar'

Then, syncdb will create in SQLite3 the following intermediate table. Note that there is no REFERENCES constrains defined.

CREATE TABLE "foo_bars" (
    "id" integer NOT NULL PRIMARY KEY,
    "foo_id" integer NOT NULL,
    "bar_id" integer NOT NULL,
    UNIQUE ("foo_id", "bar_id")
)
CREATE INDEX "foo_bars_e69b476d" ON "foo_bars" ("bar_id");
CREATE INDEX "foo_bars_ef315d12" ON "foo_bars" ("foo_id")

If I define ManyToManyField in Bar class as opposed to Foo class, such as

class Foo(models.Model):
    class Meta:
        db_table = 'foo'

class Bar(models.Model):
    foos = models.ManyToManyField(Foo)
    class Meta:
        db_table = 'bar'

Then, the intermediate table becomes:

CREATE TABLE "bar_foos" (
    "id" integer NOT NULL PRIMARY KEY,
    "bar_id" integer NOT NULL,
    "foo_id" integer NOT NULL REFERENCES "foo" ("id"),
    UNIQUE ("bar_id", "foo_id")
)
CREATE INDEX "bar_foos_e69b476d" ON "foo_bars" ("bar_id");
CREATE INDEX "bar_foos_ef315d12" ON "foo_bars" ("foo_id")

Now only foo_id has REFERENCES constraint.

If I choose MySQL instead of SQLite3, syncdb creates REFERENCE constrains for both foo_id and bar_id, as follows:

CREATE TABLE `bar_foos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bar_id` int(11) NOT NULL,
  `foo_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bar_id` (`bar_id`,`foo_id`),
  KEY `bar_foos_e69b476d` (`bar_id`),
  KEY `bar_foos_ef315d12` (`foo_id`),
  CONSTRAINT `bar_id_refs_id_9d8d0296` FOREIGN KEY (`bar_id`) REFERENCES `bar` (`id`),
  CONSTRAINT `foo_id_refs_id_27f38014` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`)
) ENGINE=InnoDB

So, there seems an issue when syncdb creates an intermediate table derived from ManyToManyField for SQLite3.

Change History (2)

comment:1 Changed 2 years ago by Baptiste Mispelon

Description: modified (diff)
Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset

Hi,

Indeed, there seem to be a difference between the two (it looks like an ordering issue where the tables are created in an order that prevents sqlite from referencing the model tables from the m2m one).

However, this issue is gone if you use the new migrations framework in 1.7 so I'm tempted to mark this ticket as wontfix.

What do you think?

comment:2 Changed 2 years ago by Tim Graham

Resolution: wontfix
Status: newclosed
Note: See TracTickets for help on using tickets.
Back to Top