Opened 11 years ago

Closed 11 years ago

#1892 closed defect (invalid)

The sqilite backend fails to mark fields as foreign keys

Reported by: anonymous Owned by: Adrian Holovaty
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:


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:

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:

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.

Change History (3)

comment:1 Changed 11 years ago by anonymous

Cc: jwm@… added

comment:2 Changed 11 years ago by anonymous

Sorry, but sqlite does not enforce foreign keys at all, see


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 11 years ago by Adrian Holovaty

Resolution: invalid
Status: newclosed

Marking as invalid. See previous comment.

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