Re: When to use cascading deletes?

On Jun 11, 2009, at 10:59 AM, David wrote:

> Hi there.
>
> When is a good time to use cascading deletes?

As a general rule of thumb I use cascading deletes if the data in a  
record is meaningless without the record that the foreign key points to.

> Ideally, I'd like postgresql to not do cascading deletes, *except*
> when I tell it to, and the rest of the time fail when the user didn't
> explicitly "opt in" for cascading deletes.

That's the default behaviour. If you don't specify what to do on  
DELETE (or UPDATE) then postgresql defaults to 'NO ACTION', which  
means it will not allow the delete if there are records referencing  
the row you try to delete.

> I don't know, maybe I have the wrong mindset, and cascading is
> preferable (in terms of object model) in some cases? I'd like to read
> more on this subject (general best practices for what types of
> cascading are appropriate to use when).

It's not an object model, it's a relational model. In a relational  
model cascading makes sense, as it's a property of the relation  
between records in two (or more) tables.

I often notice people get confused about the direction of a cascade.  
If you define a foreign key with a cascade (whether on UPDATE or  
DELETE doesn't matter), then the cascade fires on operations on the  
_referred_ record, not on operations on the _referring_ record.

Basically I determine which cascading method to use by how records are  
related:

If the referring record only has any meaning in relation to the  
referred record, then you use CASCADE on deletes. A typical use case  
us a connection table in a many-to-many relationship.

If the referring record has a meaning regardless of whether the  
referred record exists, then you use SET NULL. For example, if you  
have a relation between an employee and a desk, the desk still exists  
after you fire the employee.

If the referring table is important to your data then you don't want  
to be allowed to delete the referred records while there are records  
referring them. Typically this is the case if most of the meaning of a  
relation is in the referring tables. Often these are tables referring  
to user records; such a database generally isn't about the users, but  
about what we want to store ABOUT them. The users are just  
placeholders to be able to make a distinction between them. In this  
case your application needs to be able to deal with a foreign key  
constraint violation or have some method to prevent those from occuring.

If you don't know what to choose for a given relation it's safe to  
stick with the default, but you do need to think about what your  
application needs to do in such cases.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4a30d738759151089568354!