#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 bmispelon)

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 11 months ago by bmispelon

  • 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 10 months ago by timgraham

  • Resolution set to wontfix
  • Status changed from new to closed
Note: See TracTickets for help on using tickets.
Back to Top