[Database]Follow up question about [delete(cascade/retrict)]and Update(cascade/restrict)?

Using the cascading operators on foreign key constraints is a bit of a hot topic In theory, if you know for a fact that deleting a parent object will automatically also mean deleting all its children objects, then cascading delete on the link between child and parent table might make sense Imagine a "machine" that consists of "parts". If your logic says that if the machine is deleted, all the parts making up that machine are also to be purged from the database, then you could use a cascading delete option on the foreign key link between the parts table and the machine table However: this can be a bit tricky, especially if you end up having a whole chain of tables that are linked with this option. Therefore, a lot of developers tend to prefer to handle this in their own data access code, rather than define it in the database The update cascade is typically used when a primary key on a parent table changes - in order to update all related child tables and rows to reflect that change.

This is typically considered a database code smell - the better option here is to make sure the primary key never changes, so that this cascading update is never needed - e.g. By introducing an artificial "surrogate" key column into your table, which has no business-related meaning and thus never gets updated Does that help at all? Any particular detail you're still unclear on? My take is this: while this is a great idea in theory, most developers don't really use this in "real live" - most developers will choose to handle this in data access code, which gives them full and explicit control over what gets deleted (or updated).

Using the cascading operators on foreign key constraints is a bit of a hot topic. In theory, if you know for a fact that deleting a parent object will automatically also mean deleting all its children objects, then cascading delete on the link between child and parent table might make sense. Imagine a "machine" that consists of "parts".

If your logic says that if the machine is deleted, all the parts making up that machine are also to be purged from the database, then you could use a cascading delete option on the foreign key link between the parts table and the machine table. However: this can be a bit tricky, especially if you end up having a whole chain of tables that are linked with this option. Therefore, a lot of developers tend to prefer to handle this in their own data access code, rather than define it in the database.

The update cascade is typically used when a primary key on a parent table changes - in order to update all related child tables and rows to reflect that change. This is typically considered a database code smell - the better option here is to make sure the primary key never changes, so that this cascading update is never needed - e.g. By introducing an artificial "surrogate" key column into your table, which has no business-related meaning and thus never gets updated. Does that help at all?

Any particular detail you're still unclear on? My take is this: while this is a great idea in theory, most developers don't really use this in "real live" - most developers will choose to handle this in data access code, which gives them full and explicit control over what gets deleted (or updated).

Thanks for the response, got the idea of cascading delete/update. Really appreciate for the response. – John Sep 26 '10 at 15:15.

As well as what marc_s says.. I've found that CASCADE options can sometimes generate "dummy" updates to a child table when there was no need thus prolonging a transaction, using more locks, using more resources etc.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.