Opened 12 years ago
Closed 11 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: | dev |
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 )
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 by , 12 years ago
Description: | modified (diff) |
---|
comment:2 by , 12 years ago
Triage Stage: | Unreviewed → Accepted |
---|---|
Type: | Uncategorized → Bug |
comment:3 by , 12 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:4 by , 12 years ago
Owner: | removed |
---|---|
Status: | assigned → new |
Note:
See TracTickets
for help on using tickets.
+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.