Opened 4 years ago

Closed 3 years ago

#19659 closed Bug (duplicate)

Foreign keys not generated properly on SQLite

Reported by: Florian Apolloner Owned by:
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: Florian Apolloner Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by 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 :)

Change History (6)

comment:1 Changed 4 years ago by Florian Apolloner

Description: modified (diff)

comment:2 Changed 4 years ago by Anssi Kääriäinen

Triage Stage: UnreviewedAccepted
Type: UncategorizedBug

+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.

comment:3 Changed 4 years ago by tga

Owner: changed from nobody to tga
Status: newassigned

comment:4 Changed 4 years ago by tga

Owner: tga deleted
Status: assignednew

comment:5 Changed 3 years ago by Ramiro Morales

Is't this a duplicate of #14204 ?

comment:6 Changed 3 years ago by Simon Charette

Resolution: duplicate
Status: newclosed

It is.

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