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

[Annadel]

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.

[Baptiste Mispelon]

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?