My rule of thumb for when to use to not use cascading deletes is this:
If the what the record represents can essentially be thought of a "part
of" what the record that it references represents, I use cascading
deletes. If what the record represents has an independent existence,
that it, it does not necessarily have the same life cycle, I prohibit
cascading. (This is more or less the distinction between composition and
aggregation in UML terms, if I remember correctly.)
This amounts to the same advice others have already given, but in other
terms, and may be helpful if you conceive of your data this way.
Eric
David wrote:
> Hi there.
>
> When is a good time to use cascading deletes?
>
> Usually, for safety reasons, I prefer to not ever use cascading
> deletes. But that can lead to some complex code that uses topological
> sorts etc to manually delete records in the right order, when a
> cascading delete is needed.
>
> 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. When it comes to enabling
> cascading deletes, I don't really like the idea that deleting or
> updating a row from one table can have a possibly unexpected (to the
> app programmer, using the database) chain reaction to other tables.
>
> 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).
>
> Any tips?
>
> Thanks,
>
> David.
>
>