SQLite support for FK constraints
Ramiro Morales
SQLite 3.6.19 from Oct 14 2009 added support for enforcing these
constraints. The relevant documentation is available at
http://www.sqlite.org/foreignkeys.html
I've opened ticket [1]14204 with a initial patch.
In short, the SQLite feature has the following particularities:
* There is no need to defer the declaration for these constraints to
avoid references to tables still no created because no check is
performed at CREATE TABLE time.
* In fact, there is no support in ALTER TABLE for adding constraints
after the fact.
* It supports deferring the constraint enforcement until the end of a
transaction (by using 'DEFERRABLE INITIALLY DEFERRED' in the
FOREIGN KEY declaration).
* It has no support for DROP CONSTRAINT so if a table of a model pointed
to by a FK is deleted there is no way to delete the constraint
(beforehand of afterwards). This leaves us in the situation in which we
can easily create constraints and instruct SQLite to enforce them but
we can't remove them.
As this delete-this-table functionality is currently being used
exclusively for DB teardown performed by tests
(DatabaseCreation.sql_destroy_model() method), I' ve chosen to use ON
DELETE CASCADE option when creating the FK to avoid table deletion
failures caused by referencial integrity errors because in such
scenarios normally all the tables of the DB are being removed anyway
As you know, during normal usage of the ORM, Django currently performs
its own collection of reverse FK and cascade deletes them so the point
where cascade deletion is done by SQLite should never be reached
(famous last words). I'm still not totally sure this is safe.
* Availability of this feature can be queried and toggled by using the
'foreign_keys' pragma. Old versions of SQLite or new versions that
were compiled with the feature disabled can be detected and starting
with that information, it can be turned on/off explicitly.
At this point the following doubts remain:
Should we:
* Create another backend that defers most of its functionality to the
'sqlite3' one and only change behavior to make use (and require?)
the feature in SQLite?
* Extend the current backend to detect the SQLite support is available,
and if so change behavior to make use of it?. This is what the
current patch does.
* As above and additionally provide a way to control with a backend
boolean switch [2]option?. Also, can/should the default value for this
option be scheduled to be changed at a future Django release and
providing a migration path?
Did I miss too many design considerations?
Regards,
--
Ramiro Morales� |� http://rmorales.net
1. http://code.djangoproject.com/ticket/14204
2. http://docs.djangoproject.com/en/1.2/ref/settings/#options
Russell Keith-Magee
I'd be inclined to follow option 3. For backwards compatibility, this
is something that should be disabled by default, regardless of the
capabilities of the SQLite instance. DATABASES[]['OPTIONS'] can then
be used to turn the feature on; if the database doesn't support the
feature (i.e., pre 3.6.19), an exception would be raised. At some
later version when (version >= 3.6.19) has decent market penetration,
we can make a decision about whether we're going to change the default
value.
Given that SQLite parses but ignores REFERENCES statements (et al), I
don't see any harm in always producing those statement; essentially,
that makes the SQL generation comparable with all the other backends,
but the database option determines whether the statements are silently
parsed and ignored, or actually used.
There's also going to be a need for some very clear migration
instructions in the release notes, including instructions on how to
add the relevant constraints to an existing database.
Other than that, it sounds like you've pretty much got it covered. Great work!
Yours,
Russ Magee %-)