Django isn't creating db constraints for foreign keys with SQLite

Question

I'm using Django 3.0.6

I'm adding ForeignKey and ManyToManyField to my models, but I've noticed that django creates the INDEX, but not the actual FOREIGN KEY constraints in the db.

I've tried to explicitly set db_constraint=True but as expected is useless, since it's True by default.

I've found so many answers explaining this, but only for very old Django versions, doing tricks for enabling it when it was otherwise disabled. Now instead it should just work out of the box. Couldn't find anything AT ALL regarding Django 3.

Code

class Token (models.Model):
    owner = models.ForeignKey(Chiefdom, on_delete=models.CASCADE, db_constraint=True)
    county = models.ManyToManyField(County, db_constraint=True)
    amount = models.PositiveSmallIntegerField()

SQLite

CREATE TABLE IF NOT EXISTS Piece_token (
  id integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  amount smallint unsigned NOT NULL,
  owner_id integer NOT NULL
);

CREATE INDEX IF NOT EXISTS Piece_token_owner_id_d27c77f0 ON Piece_token (owner_id);

CREATE TABLE IF NOT EXISTS Piece_token_county (
  id integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  token_id integer NOT NULL,
  county_id integer NOT NULL
);

CREATE INDEX IF NOT EXISTS Piece_token_county_county_id_57802417 ON Piece_token_county (county_id);

CREATE INDEX IF NOT EXISTS Piece_token_county_token_id_e7798ae9 ON Piece_token_county (token_id);

CREATE UNIQUE INDEX IF NOT EXISTS Piece_token_county_token_id_county_id_b06b16cc_uniq ON Piece_token_county (token_id, county_id);

You have to explicitly enable referential integrity enforcement in SQLite https://stackoverflow.com/a/17213720/599023 — Andee, May 28 '20 at 19:54
@Andee I'm surprised because those are very old answers, and newer ones explicitly say "this is no longer needed". Except it doesn't work. — o0'., May 28 '20 at 19:58
Does this answer your question? [Does SQLite support referential integrity?](https://stackoverflow.com/questions/17213454/does-sqlite-support-referential-integrity) — iklinac, May 28 '20 at 20:01
@iklinac no, because I don't know how to make it work in django, and the linked answers do not work. — o0'., May 28 '20 at 20:04
@iklinac 3.31.1 apparently. Keep in mind it comes bundled with django (which is the whole point of django in the first place) — o0'., May 28 '20 at 20:11
sqlite is just file nothing special about it, i don't see why would it be point of Django, it is just there to tinker something out fast if you need. There is a little to no people using it for production or even proper development — iklinac, May 28 '20 at 20:37
Yeah sorry, I mean the sqlite libs accessing it. I didn't realise you meant the version of the db itself. I know it's not often used in production, but I find it quite useful in many circumstances, and I'm certainly now planning to switch to something else in this project :/ — o0'., May 28 '20 at 20:43

score 0 · Answer 1 · answered May 28 '20 at 20:34

0

I have checked now with same version of Django and SQLite there are all foreign keys present

For example

SELECT * FROM pragma_foreign_key_list('auth_user_groups');

Note all foreign keys are deferred and checked from Django -> source

answered May 28 '20 at 20:34

iklinac

14,944
4
28
30

Django isn't creating db constraints for foreign keys with SQLite

Code

SQLite

1 Answers1