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!