Code

Opened 8 years ago

Closed 8 years ago

#1892 closed defect (invalid)

The sqilite backend fails to mark fields as foreign keys

Reported by: anonymous Owned by: adrian
Component: Database layer (models, ORM) Version: master
Severity: major Keywords:
Cc: jwm@… Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

The sqlite table creation backend appears to only include the REFERENCES table (key) SQL if the table being referenced has a name that appears alphabetically before the table with the foreign key reference. An example; a model such as this:

from django.db import models

# Create your models here.

class Alpha(models.Model):
    name = models.CharField(50)

class Gamma(models.Model):
    name = models.CharField(50)

class Beta(models.Model):
    name = models.CharField(50)
    alpha = models.ForeignKey(Alpha)    
    gamma = models.ForeignKey(Gamma)    

Produces this sql:

BEGIN;
CREATE TABLE "sqlite_test_alpha" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(None) NOT NULL
);
CREATE TABLE "sqlite_test_beta" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(None) NOT NULL,
    "alpha_id" integer NOT NULL REFERENCES "sqlite_test_alpha" ("id"),
    "gamma_id" integer NOT NULL
);
CREATE TABLE "sqlite_test_gamma" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(None) NOT NULL
);
-- The following references should be added but depend on non-existant tables:
COMMIT;

Observe that gamma_id in the beta table lacks a REFERENCE.

I don't know enough about sqlite to know if this actually matters, but it does defy my expectations of what a foreign key field ought to be defined as.

Past my bed time now, but I'll look into the documentation and code tomorrow, and see if I can come up with a patch.

Attachments (0)

Change History (3)

comment:1 Changed 8 years ago by anonymous

  • Cc jwm@… added

comment:2 Changed 8 years ago by anonymous

Sorry, but sqlite does not enforce foreign keys at all, see http://www.sqlite.org/omitted.html

quoting:

SQL Features That SQLite Does Not Implement
Rather than try to list all the features of SQL92 that SQLite does support, it is much easier to list those that it does not. Unsupported features of SQL92 are shown below. The order of this list gives some hint as to when a feature might be added to SQLite. Those features near the top of the list are likely to be added in the near future. There are no immediate plans to add features near the bottom of the list.

FOREIGN KEY constraints FOREIGN KEY constraints are parsed but are not enforced.

comment:3 Changed 8 years ago by adrian

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

Marking as invalid. See previous comment.

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.