Code

Opened 15 months ago

Closed 4 months ago

#19659 closed Bug (duplicate)

Foreign keys not generated properly on SQLite

Reported by: apollo13 Owned by:
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: apollo13 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 apollo13)

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 :)

Attachments (0)

Change History (6)

comment:1 Changed 15 months ago by apollo13

  • Description modified (diff)

comment:2 Changed 15 months ago by akaariai

  • Triage Stage changed from Unreviewed to Accepted
  • Type changed from Uncategorized to Bug

+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 14 months ago by tga

  • Owner changed from nobody to tga
  • Status changed from new to assigned

comment:4 Changed 14 months ago by tga

  • Owner tga deleted
  • Status changed from assigned to new

comment:5 Changed 4 months ago by ramiro

Is't this a duplicate of #14204 ?

comment:6 Changed 4 months ago by charettes

  • Resolution set to duplicate
  • Status changed from new to closed

It is.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.