Foreign keys not generated properly on SQLite

[Florian Apolloner]

Depending on the order of apps in INSTALLED_APPS and what is already in the db ForeignKeys are not generated as foreign keys but only as the datatype which the foreign key should refer to:

$ rm bla.sqlite3 # Get rid of the database
$ ./manage.py syncdb
Creating tables ...
Creating table auth_permission ### auth is first in INSTALLED_APPS
Creating table auth_group_permissions
Creating table auth_group
Creating table auth_user_groups
Creating table auth_user_user_permissions
Creating table auth_user
Creating table django_content_type
Creating table django_session
Creating table django_site
Creating table testapp_test1 ### testapp is last in INSTALLED_APPS
Creating table testapp_test2

$ sqlite3 bla.sqlite3 
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema testapp_test1
CREATE TABLE "testapp_test1" (
    "id" integer NOT NULL PRIMARY KEY,
    "fk_id" integer NOT NULL REFERENCES "auth_user" ("id") ### foreign key is created properly
);
CREATE INDEX "testapp_test1_256ac373" ON "testapp_test1" ("fk_id");
sqlite> 
$ vim djtest/settings.py 
$ ### ^ moved testapp to top in INSTALLED_APPS
$ rm  bla.sqlite3 # Clean database again.
$ ./manage.py syncdb
Creating tables ...
Creating table testapp_test1 ### Now testapp is create before auth
Creating table testapp_test2
Creating table auth_permission ### Auth creation starts here
Creating table auth_group_permissions
Creating table auth_group
Creating table auth_user_groups
Creating table auth_user_user_permissions
Creating table auth_user
Creating table django_content_type
Creating table django_session
Creating table django_site

$ sqlite3 bla.sqlite3 
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema testapp_test1
CREATE TABLE "testapp_test1" (
    "id" integer NOT NULL PRIMARY KEY,
    "fk_id" integer NOT NULL ### foreign key is not generated
);
CREATE INDEX "testapp_test1_256ac373" ON "testapp_test1" ("fk_id");
sqlite> 

Can we do something against this? Can this also happen with other databases and as such threaten referential integrity?

EDIT:// MySQL and postgres seem to do what they should, we could enable ​http://www.sqlite.org/foreignkeys.html for SQLite versions which does support that :)

[Anssi Kääriäinen]

+1 for enabling foreign keys when they are available. We should make this default with an opt-out for those who can't use this for backwards compatibility.

Currently omitting the foreign key declarations isn't too serious, as the foreign keys aren't enforced. When we enable foreign keys, we should of course also fix this issue.

[Ramiro Morales]

Is't this a duplicate of #14204 ?

[Simon Charette]

It is.