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! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
