id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
23279	REFERENCES constraints are not set to the fileds in an intermediate table created by syncdb for SQLite3	Annadel	nobody	"If I have the following class definition:
{{{#!python
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.

{{{#!sql
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 

{{{#!python
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:

{{{#!sql
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:

{{{#!sql
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."	Bug	closed	Uncategorized	1.6	Normal	wontfix	syncdb manytomany		Unreviewed	0	0	0	0	0	0