Archive for category MySQL
Update 1 Apr 2011: This issue has been resolved in Django 1.3.
I discovered “accidentally” recently that the Django model delete() method not only deletes the model instance, but all its related objects — at least those which are related to the original object via a ForeignKey field. (The source code is so labyrinthine that I gave up trying to determine exactly what it does.) This is not necessarily a bad thing; many times it’s exactly what you want to preserve the “referential integrity” of your data. For instance, if you have some kind of “collection” object and a number of “item” objects which are related to it in a one-to-many relationship, it’s reasonable to expect that when the collection is deleted, the related items are also deleted.
Now, in the Django admin app, when you click the button to delete an item you get a helpful warning informing you of all the other objects you will delete if you proceed. But what if you’re using the API directly? No warning there, it just whacks the whole lot. At first I thought maybe this was an ORM thing, but it’s not. In fact it has nothing to do with the backend database: Django manually fetches all the related objects and deletes them. For example, in my case the backend is MySQL 5.0 and MyISAM tables. In MySQL 5.0 only InnoDB tables support foreign key constraints; MyISAM tables will parse the syntax but do nothing with it. In any case, the constraints that Django generates do not include an ON DELETE clause, so MySQL 5.0 would use “RESTRICT” as the default value, meaning that the database will not allow the deletion of a row from the “parent” table if the primary key value exists in the referenced foreign key of a row in the related table. I suppose that’s good as far as it goes, but you shouldn’t be messing with the database directly, right? Anyway, that’s not relevant because we’re not talking about raw SQL commands, but the Django model API methods.
So, there’s an outstanding proposal to add keyword arguments to the ForeignKey model field to control how DELETE and UPDATE on the parent model affect the related model. The status of the proposal is unclear and there hasn’t been a lot of serious discussion on the mailing lists AFAICT. I would guess that since you can override the delete() method on a per-model basis, and presumably other issues are more pressing, that the core developers don’t want to worry about this right now. They may be right, but I sure had an unpleasant surprise when I discovered this behavior the wrong way.
I have an organizational directory database with Persons and OrgUnits. Persons are automatically added and removed based on information retrieved an another data source (LDAP). An OrgUnit (e.g., a department) can have a “head”, which is a person:
head = models.ForeignKey(Person, blank=True, null=True, related_name='head_of')
Now, of course, if a Person who happened to be the head of a department left the organization I wouldn’t want the department to be deleted, right? In this case, as it turns out, it was worse than that. Since the OrgUnit structure is hierarchical, the OrgUnit model has a one-to-many relationship with itself:
parent = models.ForeignKey('self', null=True, blank=True, related_name='children')
Now what happens if the head of a top-level OrgUnit is deleted? The OrgUnit of which he/she was head is deleted, and every “descendant” OrgUnit under that one! So, I had to override the delete() methods on both the Person and OrgUnit models.
def delete(self): """ Override default model method so an OrgUnit is not deleted when its head is deleted. """ self.head_of.clear() super(Person, self).delete()
def delete(self): """ Override default model method so that OrgUnit children are not deleted when the parent OrgUnit is deleted. """ self.children.clear() super(OrgUnit, self).delete()
On balance, Django’s default behavior is probably the right thing to do — as long as you’re aware of it!