On Thu, Jun 11, 2009 at 9:59 AM, David<wizzardx@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
