Opened 5 years ago

Last modified 11 days ago

#14204 assigned New feature

Take advantage of SQLite support for FK constraints

Reported by: ramiro Owned by: coldmind
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords: sqlite, foreign key
Cc: me@… Triage Stage: Accepted
Has patch: yes Needs documentation: yes
Needs tests: yes Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description (last modified by ramiro)

SQLite3 3.6.19 from Oct 14 2009 adds support for enforcing these constraints. See http://www.sqlite.org/foreignkeys.html

Creating this ticket to track this feature, with an initial implementation patch to get feedback. Will also open a django-dev thread.

Attachments (1)

14204-sqlite-fk-constraints.diff (5.0 KB) - added by ramiro 5 years ago.
First implementation

Download all attachments as: .zip

Change History (17)

Changed 5 years ago by ramiro

First implementation

comment:1 Changed 5 years ago by ramiro

  • Needs documentation set
  • Needs tests set
  • Patch needs improvement unset

See also #5698 and #12210.

comment:2 Changed 5 years ago by russellm

  • Triage Stage changed from Unreviewed to Accepted

Accepted on principle; however, we need to be clear on the backwards compatibility consequences of this change. In particular, I'm concerned about automatically enabling "PRAGMA foreign_keys" just because the feature is available.

comment:3 Changed 5 years ago by ramiro

  • Summary changed from Take advantage of (newish) SQLlite support for FK constraints to Take advantage of (newish) SQLite support for FK constraints

comment:4 Changed 4 years ago by graham_king

  • Keywords sqlite foreign key added
  • Severity set to Normal
  • Type set to New feature

comment:5 Changed 3 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:6 Changed 3 years ago by aaugustin

  • Easy pickings unset

Change Easy pickings from NULL to False.

comment:7 Changed 17 months ago by charettes

#19659 was a duplicate.

comment:8 Changed 16 months ago by ramiro

  • Description modified (diff)

comment:9 Changed 16 months ago by Ramiro Morales <cramm0@…>

In 5782c94f2382303dd28a35a9b9591ad480286220:

Added generation of SQLite FK DDL in initial migrations.

SQLite accepts the relevant standard SQL (although by default it doesn't
enforce the constraint), and the 'traditional' creation backend helper
generate it, so this allows us to:

  • Maintain the status quo
  • Improve readability of the SQL code generated for that backend.

Also, we will need this for when we fix Refs #14204.

comment:10 Changed 9 months ago by timo

  • Patch needs improvement set
  • Summary changed from Take advantage of (newish) SQLite support for FK constraints to Take advantage of SQLite support for FK constraints

5 years later (by the time Django 1.8 will be released), I think we could probably add this unconditionally and say that SQLite versions that don't support FKs aren't supported. Of course, we will still have to consider existing databases for which support isn't enabled.

comment:11 Changed 9 months ago by akaariai

How about we add a database OPTION key for SQLite to allow disabling foreign keys (mainly usable for backwards compatibility). If "disable foreign keys" flag isn't set, then starting in Django 1.8 automatically enable foreign key support.

comment:12 Changed 9 months ago by aaugustin

Since it's called an ORM, I'm theoretically in favour of focusing on relational software. Realistically, the main problem is MyISAM. It was MySQL's default until 5.5. As long as we can't drop the code that works around databases not enforcing constraints, there's less to gain by requiring FKs on SQLite.

I'm +1 on enabling FK checks by default in Django and +0 on requiring it unconditionnally.

comment:13 Changed 5 weeks ago by wkschwartz

+1 on getting this patch merged in. For what it's worth, the patch's implementation of _check_fk_constraint_support looks correct to me based on my reading of the SQLite documentation. My only question about the patch is whether the PRAGMA needs to be issued on every new connection as the patch does, or only once when the database is created.

comment:14 Changed 5 weeks ago by timgraham

If you'd like to see it merged, feel free to update it to apply cleanly and add documentation (release notes). Then send a pull request and update the ticket flags so the patch appears in the review queue.

comment:15 Changed 5 weeks ago by aaugustin

The pragma is per-connection. It's a runtime property of the SQLite engine. It doesn't affect the on-disk file format.

comment:16 Changed 11 days ago by coldmind

  • Cc me@… added
  • Owner changed from nobody to coldmind
  • Status changed from new to assigned

I'm working on finishing of this patch

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