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

Question

I would like to know when to actually use delete cascade or delete restrict AND update cascade or update restrict. I'm pretty confused using them or applying in my database.

Answer 1

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).

Answer 2

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.