Tuesday, August 3, 2010

Be explicit about OnDelete in doctrine schemas

In doctrine, I tried $obj->delete(); and got an SQL exception from another table. But you're supposed to have deleted that too, that's the whole point of me telling you about all the relations between the tables, screamed I.

So I went through my schema inserting "onDelete: CASCADE" in every relation. But as I did it realized sometimes I wanted "onDelete: SET NULL", and sometimes I wanted "onDelete: RESTRICT". This is a helpful article on the onDelete choices (though for null you actually have to write "SET NULL" not just "NULL". There is also another choice: "OnDelete: SET DEFAULT" (e.g. when a user is deleted, you may want his comments to become owned by the admin user, rather than become orphaned.)

So, now my advice is to explicitly put onDelete on every relation; treat it as required. I also find I need a line of documentation to explain every time I choose something other than cascade. It is good to be forced to think of these things.

All is smooth until you come to many-to-many relationships, especially self-referential (nest) relationships, such as two users being friends (symmetrical) or one user blocking another (one-way). I will cover it in a separate article.


Richard said...

Unfortunately the link you referenced to the old post is no longer available, but you're summary was helpful anyways - thanks :)

This google groups thread and this blog article also came in handy.

darren said...

Thanks Richard; I couldn't find that article cached anywhere else either. (Though it is not missing, just the server is not responding, so it may just be a temporary outage.)
Your second link is interesting, regarding soft delete. It is easy to overlook these things.