Opened 10 years ago

Last modified 10 years ago

#23279 closed Bug

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

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 (1)

comment:1 by Baptiste Mispelon, 10 years ago

Description: modified (diff)

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?

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