Provide an equivalent for 'ON DELETE' and friends

[James Bennett]

A couple threads on django-users have asked about deleting an object while preserving related objects, preferably by setting their foreign key fields to NULL, but AFAIK we don't expose that functionality in any way.

So, a proposed enhancement to model definitions. ForeignKeyField should take two extra optional arguments:

on_delete -- value is one of: cascade, restrict, set_null, set_default. If it's set_default, a default value must be supplied. I'm thinking we can get by with just restrict and not also no_action, but open to arguments about it.

on_update -- same as above.

These constraints should be output in the SQL for the databases which support them, but either way they should always be respected by Django.

[James Bennett]

Actually.

For on_update there would be the additional possible value of update.

[Jeff Forcier]

I have an old patch that addresses part of this issue, #1007

[notnotpeter <petertripp@…>]

I'm definitely interested in this feature. Please excuse my postgresql centered mindset, it's been my db of choice since around 1998/1999 and as a result I think in postgres and everything else is a translation. Apologies for this being so long and the formating sucking...damn you WikiFormating.

I think we can support it better than originally thought.
Postgres is hella easy;
MySQL is easy if the user is using InnoDB tables and users trying to use MyISAM tables should be taken out back and shot. (Why run a database if it doesn't support fk's!)
SQLLite support isn't hat hard, if the user's version of SQLLite supports triggers (1.5). Although integrity is not guaranteed, it never really is in SQLLite.

Here are the five possible actions for ON UPDATE / ON DELETE
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

NO ACTION and RESTRICT are similar. In postgres, restrict checks immediately, whereas NO ACTION waits till the end of the transaction to do constraint checks. I'm not positive the differences in other systems that support both (most don't). Although I think RESTRICT is more intuitive for users, RESTRICT is not in the SQL92 standard, only NO ACTION. As the parent mentioned, I think Django should only support one or the other and let the database abstraction layer handle what actually gets sent to the database. The differences aren't that great; but will be more apparent once proper transaction support is added.

---

Database particulars (what support databases have).

Postgresql: supports all five.

mySQL (non-innoDB): Foreign keys aren't really supported, but we could do it with triggers (HELL NO!) -- see references.

mySQL (innoDB): CASCADE / SET NULL / RESTRICT work. no support for SET DEFAULT; NO ACTION != RESTRICT. It should be (as mysql doesn't support delayed constraint checking), but in some circumstances (older versions) NO ACTION doesn't restrict (as per SQL92), it just does nothing. With mySQL, we should probably always use RESTRICT and never NO ACTION.

SQLLite: no support for foreign keys (officially). But we could use triggers to simulate them (introduced SQLLite v1.5) See "Foreign key trigger generator" in references.

MS SQL 7: no support for the ON UPDATE/ON DELETE clause at all.

MS SQL 2000: supports only NO ACTION and CASCADE.

MS SQL 2005: suppors everything but no support for RESTRICT (use NO ACTION instead)

Interbase/Firebird: Interbase didn't have support for RESTRICT. Not sure about firebird. (use NO ACTION instead)

Oracle: I'm pretty sure it supports them all. Don't quote me on that, I'm no Oracle guru.

---

References:
MySQL reference: ​http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

MySQL FK trigger implementation: ​http://dev.mysql.com/tech-resources/articles/mysql-enforcing-foreign-keys.html

MSSQL 2005: ​http://msdn2.microsoft.com/en-us/library/ms186973.aspx

SQLLite:

Foreign key trigger docs: ​http://www.sqlite.org/cvstrac/wiki/wiki?p=ForeignKeyTriggers

Foreign key trigger generator: ​http://www.rcs-comp.com/site/index.php/view/Utilities-SQLite_foreign_key_trigger_generator

Wow, this is like 4x longer than I intended it to be, sorry for the rambling. I wish I knew enough to try and contribute this. I'd be happy to test when someone gets some patches together.

[James Bennett]

Peter,

Would you mind posting this to the Django developers list? Some of this material has already been covered there, but reviving the discussion might encourage work on it.

[Malcolm Tredinnick]

When we come up with a reasonable solution for this, we also need to work out how this is going to impact developers. If they try to delete an object and cascading deletes aren't enabled, will the developer be responsible for cacthing the IntegrityError, or will that be handled by the delete_object code?

No opinion on this yet, just transferring the issue from #1320 so I can close that off.

[gabor@…]

for example, in the project where i work currently,
having to catch the integrityError would be perfectly fine.

in my case basically i do not want to enable the deleting of objects when their deletion would delete also other objects.
so having this integrityError exception is completely fine. (i would catch it, and display an user-friendly error-message).

(i just wanted to provide an example-situation)

of course, more complex handling of these situations would be nice too, but even the integrityError solution helps a lot,
because currently there's just no way to do it.

(a completely different direction to approach this problem would be to document the code that django uses to determine
which objects to "cascade-delete", so that we could call that method, and find out if there are any "depending" objects.
but of course the integrityError solution is more robust)

[anonymous]

Perhaps a temporal solution was to provide a "blank" method that blanks all the relations of one object to the others: For example, if we have:

class Person(model.Models):

firstname = models.CharField(max_length=100)
lastname = models.CharField(max_length=100)
car = models.ManyToManyField('Car')

class Car(models.Models):

name = models.CharField(max_length=100)
number_plate = models.IntegerField()

one_car = Car(name="Porche", number_plate="COOL")
one_car.save()
this_is_me = Person(firstname="John", lastname="Doe", car=one_car)
this_is_me.save()

Now I have not this car (I sell it but the car exists). I just want to delete my relation to this car. It could be done by:

this_is_me.Car_set.blank(one_car) # This drops one_car with the lists of cars I have
this_is_me.Car_set.all.blank() # This is equivalent that I have no cars (but cars exist)

I suppose that this syntax is not good at all, but the main idea as you understand I say you: one method that deletes the relations between objects and not the objects itselfs.

Thanks,
Xan.

[James Bennett]

Related discussion and proposals are in #1007, which was closed in favor of this ticket.

[Jacob]

I think you can accomplish the same thing by listening to pre-delete signals and doing that work there. I'm not a big fan of trying to duplicate ON DELETE ... in Django, so I'm going to wontfix this. If signals *can't* do this job, we should open a ticket about that (slightly different) problem.

[Gábor Farkas <gabor@…>]

i think simply using pre_delete signals is not enough, because the which-objects-will-django-delete algorithm is in a private (and therefore undocumented) function.

for example, let's talk about implementing an "on delete restrict" situation,
so when i call delete() on an object, and it has any related-objects, the call should fail (with an exception for example), and nothing should be deleted.

(btw. can a listener abort the processing? can i somehow inform django in a pre_delete listener that he should not delete the object? for the following i assume that it's possible.)

i assume it would be done the following way:

1. i listen to the pre_delete signal.
2. when i'm called, i check which objects would django normally delete
3. based in the information i got in step_2, i either continue or not

the problem is step step_2, because for this, django calls the _collect_sub_objects() function,so i also have to call it. but this is bad, because now i'm relying on an internal implementation detail, which might change anytime without notification.

so, while i agree that doing it with pre-delete is a great idea, some changes are necessary for this.

i see 2 possibilities (i haven't used the django-signals yet, so maybe some of these are not possible):

1. change the pre_delete signal, to also send the list of "related objects that will be deleted" to the listeners.

2. django should make _collect_sub_objects "not-private", and document it that this function is responsible for collecting the related objects.

in other words, deleting objects is a 'dangerous' operation. so if i implement some code, that makes sure that only objects that do not have related objects are deleted, i want to be very sure that the list of "related objects", that i calculate, is exactly the same as the ones that django calculates.

[Jacob]

Gábor: great points. I think the right answer is to make pre-delete send out a list of to-be-deleted objects which the receivers can modify. Can you open a new ticket to track that?

[Gábor Farkas <gabor@…>]

sure. opened #6108 with a patch.