Add ON DELETE and ON UPDATE support to Django

Can not apply the patch to latest django svn trunk (admin newforms merged). I've manually modified the files, but get the following error when trying to run the application:

 File "/usr/lib/python2.5/site-packages/django/db/models/fields/related.py", line 596, in __init__
    self.on_delete = on_delete
NameError: global name 'on_delete' is not defined

Updated patch for the latest version of Django (revision 9845 at the time the patch was created).

Is there a relevant difference between ON DELETE RESTRICT and the default? I ask because Oracle supports ON DELETE CASCADE and ON DELETE SET NULL but not ON DELETE RESTRICT. For this reason, I think it would be preferable to omit the clause entirely rather than specify ON DELETE RESTRICT.

Currently, the default behavior of Django is to emulate ON DELETE CASCADE; for compatibility reasons, I think this needs to remain the same (unless explicitly changed by the developer of a Django site). The ON DELETE RESTRICT option is necessary to tell Django to deviate from its default behavior and prevent the delete from happening rather than cascading it. This is pretty much what ON DELETE RESTRICT means in MySQL (although, now that you mention it, I think I remember that MySQL's implementation is somewhat non-standard). Is there a better wording than "RESTRICT" that means the right thing for Oracle as well as other backends? Is ON DELETE NO ACTION better?

"ON DELETE NO ACTION" is also not supported. The only way to get the "RESTRICT" behavior with Oracle is to omit the clause entirely.

If I'm understanding correctly, the difference between "RESTRICT" and "NO ACTION" is that "NO ACTION" uses deferred constraint checking. With Oracle, this is specified by omitting the "ON DELETE" clause and adding "DEFERRABLE INITIALLY DEFERRED". This is actually independent of the "ON DELETE" clause and would also affect updates.

I guess the upshot of all this is just that the Oracle backend will need to override the on_delete_and_update_clauses method.

Milestone post-1.0 deleted

Has anyone been working on this bug? I would very much like to see a current patch for it.

@Dokter Bob: This isn't a bug, it's a feature request. It's a fairly big feature request, too, so it's going to require a bit of discussion before it gets anywhere near trunk. However, now is not the time for that discussion, as we're in the final stages of the v1.1 release.

Patch updated for r10558. Very fresh and barely tested, see more info in ticket.

Note: the patch I've just uploaded does not propagate the new cascading behaviour to the admin. I am not sure whether the 'old version' of this patch did but it is something someone needs to fix. ;)

Someone requested a discussion...

I just had an encounter with this, and what I lacked was *knowing* what's going on with regards to cascading deletion, because it's quite dangerous (you loose data!!) and steals lots of time. Preventing it is simple, though. Overriding the delete() method of a model and calling clear() on the related model's foreign keys is simple, and it's a manual implementation, that all programmers should be able to understand.

But I can think of another alternative: If null=True for the foreign key in question, why not automatically use SET NULL when the related instance is deleted? For me, this is even more "intuitive" than CASCADE. After all, null=True is something that the programmer specifies and has to deal with anywhere in the implementation, which is also why he doesn't need cascading deletion of such a relation.

Also, if on_delete is possible to set in a key field, it would have to comply with the null option.. and together with the "intuition argument" that creates a 1:1 correspondence between the two options.

And then the "logic argument": Django handles its logic in Python code, not in the Database, which is kept as a simple storage engine. The RESTRICT option is a validation issue, and will probably be handled this way in most cases, so having the database enforcing it, would be redundant. To enable it on model-level could pave the way for some nice new automatic validation in ModelForms?, so I think it sounds like a nice feature.

If all this is implemented, I would suggest to remove the null option from key fields and have it set according to on_delete.

Discussion should happen on django-dev, so that everybody can be involved (not everybody monitors every single ticket). We ask that all design discussion happens there. As Russell mentioned, now is not a good time to restart the discussion, since the people with a large piece to contribute are much more focused on the next release and we know we can put this off for a few weeks or months without harm. So keep an eye on django-dev and when discussion opens up regarding 1.2 features, that's the time to pipe up, on the mailing list.

I know, but gotta serve the soup while it's still hot. I meant it as a kind of suspended discussion input, to be read in due time. Apologies if that isn't usual procedure.

Related: #6108

I'm also interested on this issue.

Very preliminary patch for 1.2 release

I've attached a new patch containing a very preliminary version of what I'm working on for possible inclusion in the 1.2 release. As discussed in the django developer mailing list some time ago, it has a much more limited scope than my original patch in these ways: 1) It removes all ON UPDATE type behavior. 2) It removes the feature that allows the backend to provide the ON DELETE support and to cause Django to generate SQL with ON DELETE clauses in it. 3) It removes the settings that could be added to the settings files to control these behaviors. It is my hope that #2, and possibly #1, can be added in future versions of Django 1.2.

The patch I've attached is very preliminary in these ways: 1) It contains some debugging code (commented out) and some TODO comments indicating things that need to be improved. 2) Some areas could use efficiency improvements. 3) It contains several intentionally failing tests illustrating problems I haven't yet solved. 4) There are no docs yet. 5) I've made no attempt to support any of the alternate terminology suggestions from this discussion: http://groups.google.com/group/django-developers/browse_thread/thread/4ece22d6053f774b/8be0b9be9b6d0b5f. This doesn't mean I won't, just that I haven't. Ideally, I'd prefer comments from more people on the suggestion that were made before choosing one of them.

I've posted this patch to give an idea what the final patch is attempting to implement and to ask for help on the problems I haven't solved yet.

What's here: OneToOne? and ForeignKey field types in models have a new attribute, on_delete. This can be set to CASCADE, PROTECT, SET_NULL, or SET_DEFAULT. If any of these attributes are present in a foreign key definition, Django implements the appropriate behavior when an attempt is made to delete an item referenced by that foreign key.

Same as previous patch, but with correct extension

@glassfordm: Is it possible that you forgot to svn add your tests before making the diff? I can't find any on_delete related tests, except a handful of low level tests for CollectedFields.

The patch I just attached is based on on_delete_on_update-r11620.diff. I made the following changes:

• Removed checks for default and null in _handle_sub_obj() (as these are static properties of fields and already checked in contribute_to_class()). These checks should perhaps be moved to django.core.management.validation.

• Moved instance-update and sql-update code inside CollectedFields so the actual data structure doesn't have to be known to code outside. Is there a reason field updates are not simply collected in CollectedObjects, too?

• Changed how update queries are performed: instead of one per instance, it now uses one (batch) per field. Except for corner cases (more than GET_ITERATOR_CHUNK_SIZE foreign keys on a single model affected), this results in fewer queries.

• Added a pony of mine: on_delete=DO_NOTHING, which would help with #10829.

I didn't write any tests (bad), but all existing tests pass. Which hopefully means that at least I haven't broken CASCADE and SET_NULL.

Regarding stale related object caches, I'd do nothing special. Django currently doesn't refresh them, so any change here (for SET_NULL or CASCADE) would be backwards incompatible. And there is no guarantee the cache won't be stale again, when _handle_sub_obj() is called anyway. So this is a documentation issue with Model.delete() at best, since QuerySet.delete() always uses "fresh" data.

Changes between 7539.on_delete.diff and 7539.on_delete.r11706.diff:

• Added tests.

• Replaced class based constants with callables that do what _handle_sub_object() did before. That cleans up _collect_sub_objects() and allows on_delete behaviors to be added without touching any existing code (I added on_delete=SET(value), because it's just a four line utility function).

• Merged CollectedFields into CollectedObjects

• Made all operations that require knowledge of internals methods on CollectedObjects. This makes django.db.models.base.delete_objects more readable.

• Couldn't resist and rewrote parts of CollectedObjects.

Changes between 7539.on_delete.r11706.diff and 7539.on_delete.r11724.diff:

• Updated to work with m2m-refactor
• Moved delete_objects() inside CollectedObjects: CollectedObjects.delete()
• Got rid of DeleteQuery.delete_batch_related(): use the normal code path for m2m intermediary models. This is required to enable on_delete for foreign keys on intermediary models. But it results in a dramatical increase of SELECT queries. Therefore ..
• Moved _collect_sub_objects() to CollectedObjects and changed its algorithm to collect related objects in batches. This reduces the number of SELECT queries for related objects. The delete call in the following example requires a total of 103 queries in trunk, while the patch only requires 4 queries: related C instances will be looked up in one bulk query.

      class A(models.Model): pass
  
      class B(models.Model):
       a = models.ForeignKey(A)
  
      class C(models.Model):
       b = models.ForeignKey(B)        
  
      a = A.objects.create()
      for i in xrange(100): B.objects.create(a=a)    
      a.delete()
  

• Introduced a batch (don't select, just delete) option to get rid of the one remaining extra query for auto_created intermediary models.
• Altered collect() to collect inheritance parents without additional queries.
• Moved CollectedObjects into its own module, as it now encapsulates the whole deletion logic and depends on db.models.sql.

TODO:

• There is an ugly contrib.contenttypes import in subqueries.py. Deleting generic relations should be handled by a custom on_delete handler instead.

Changes between 7539.on_delete.r11724.diff and 7539.on_delete.r11724.2.diff:

• Corrected the default behavior for nullable foreign keys (SET_NULL did not cause any test failures for those)
• Moved CASCADE, PROTECT, SET, SET_NULL, SET_DEFAULT, and DO_NOTHING to db.models.deletion
• Renamed CollectedObjects to Collector
• Added a couple of docstrings
• Moved sanity checks to core.management.validation

Same as my previous patch, but including unintentionally omitted unit tests