Re: When to use cascading deletes?

On Thu, Jun 11, 2009 at 9:59 AM, David<wizzardx(at)gmail(dot)com> wrote:
>
> 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.

One option would be to enable cascading deletes but not grant delete
option to the user the application connects as. So either an
administrative interface which is expected to do larger slower
operations sometimes might be allowed but the public-facing user
interface isn't allowed to accidentally delete a record which would
cause massive damage. Generally I find it makes sense to design the
database so that user-facing public interfaces can't delete much of
substance anyways.

I generally leave cascade off except for many-to-many mapping tables
which contain no additional data and are a pain to manage. Which does
sound similar to Alban's rule of thumb.

Incidentally you can avoid the topological sort by deferring
constraints and doing all the deletes in the same transaction.

--
Gregory Stark
http://mit.edu/~gsstark/resume.pdf