When/Why to use Cascading in SQL Server?

ON Delete Cascade:
-----------------
When you want *rows in child table to be deleted* If *corresponding row is deleted* in parent table.

If *on cascade delete* isn't used then an error will be raised for *referential integrity*.

ON Update Cascade:
------------------

When you want *change in primary key* to be updated in *foreign key*

If you're working on a system with many different modules in different versions, it can be very helpful, if the cascade deleted items are part of / owned by the PK holder. Else, all modules would require immediate patches to clean up their dependent items before deleting the PK owner, or the foreign key relation would be omitted completely, possibly leaving tons of garbage in the system if cleanup is not performed correctly.

I just introduced cascade delete for a new intersection table between two already existing tables (the intersection to delete only), after cascade delete had been discouraged from for quite some time. It's also not too bad if data gets lost.

It is, however, a bad thing on enum-like list tables: somebody deletes entry 13 - yellow from table "colors", and all yellow items in the database get deleted. Also, these sometimes get updated in a delete-all-insert-all manner, leading to referential integrity totally omitted. Of course it's wrong, but how will you change a complex software which has been running for many years, with introduction of true referential integrity being at risk of unexpected side effects?

Another problem is when original foreign key values shall be kept even after the primary key has been deleted. One can create a tombstone column and an ON DELETE SET NULL option for the original FK, but this again requires triggers or specific code to maintain the redundant (except after PK deletion) key value.

Summary of what I've seen so far:


* Some people don't like cascading at all.

Cascade Delete
--

* Cascade Delete may make sense when the semantics of the relationship can involve an exclusive _"is part of_" description. For example, an OrderLine record is part of its parent order, and OrderLines will never be shared between multiple orders. If the Order were to vanish, the OrderLine should as well, and a line without an Order would be a problem.
* The canonical example for Cascade Delete is SomeObject and SomeObjectItems, where it doesn't make any sense for an items record to ever exist without a corresponding main record.
* You should _not_ use Cascade Delete if you are preserving history or using a "soft/logical delete" where you only set a deleted bit column to 1/true.

Cascade Update
--

* Cascade Update may make sense when you use a real key rather than a surrogate key (identity/autoincrement column) across tables.
* The canonical example for Cascade Update is when you have a mutable foreign key, like a username that can be changed.
* You should _not_ use Cascade Update with keys that are Identity/autoincrement columns.
* Cascade Update is best used in conjunction with a unique constraint.

When To Use Cascading
--

* You may want to get an extra strong confirmation back from the user before allowing an operation to cascade, but it depends on your application.
* Cascading can get you into trouble if you set up your foreign keys wrong. But you should be okay if you do that right.
* It's not wise to use cascading before you understand it thoroughly. However, it is a useful feature and therefore worth taking the time to understand.

A deletion or update to S that removes a foreign-key value found in some tuples of R can be handled in one of three ways:

1. Rejection
2. Propagation
3. nullification.

Propagation is referred to as cascading.

There are two cases:

‣ If a tuple in S was deleted, delete the R tuples that referred to it.

‣ If a tuple in S was updated, update the value in the R tuples that refer to it.

Cascade deletes are extremely useful when implementing logical super-type and sub-type entities in a physical database.

When separate super-type and sub-type tables are are used to physically implement super-types/sub-types (as opposed to rolling up all sub-type attributes into a single physical super-type table), there is a one-to-one relationship between these tables and the issue then becomes how to keep the primary keys 100% in sync between these tables.

Cascade deletes can be a very useful tool to:

1) Make sure that deleting a super-type record also deletes the corresponding single sub-type record.

2) Make sure that any delete of a sub-type record also deletes the super-type record. This is achieved by implementing an "instead-of" delete trigger on the sub-type table that goes and deletes the corresponding super-type record, which, in turn, cascade deletes the sub-type record.

Using cascade deletes in this manner ensures that no orphan super-type or sub-type records ever exist, regardless of whether you delete the super-type record first or the sub-type record first.

I have heard of DBAs and/or "Company Policy" that prohibit using "On Delete Cascade" (and others) purely because of bad experiences in the past. In one case a guy wrote three triggers which ended up calling one another. Three days to recover resulted in a total ban on triggers, all because of the actions of one idjit.

Of course sometimes Triggers are needed instead of "On Delete cascade", like when some child data needs to be preserved. But in other cases, its perfectly valid to use the On Delete cascade method. A key advantage of "On Delete cascade" is that it captures ALL the children; a custom written trigger/store procedure may not if it is not coded correctly.

I believe the Developer should be allowed to make the decision based upon what the development is and what the spec says. A carpet ban based on a bad experience should not be the criteria; the "Never use" thought process is draconian at best. A judgement call needs to be made each and every time, and changes made as the business model changes.

Isn't this what development is all about?

I would make a distinction between

* Data integrity
* Business logic/rules

In my experience it is best to enforce integrity as far as possible in the database using PK, FK, and other constraints.

However business rules/logic IMO is best implemented using code for the reason of cohesion (google "coupling and cohesion" to learn more).

Is cascade delete/update data integrity or business rules? This could of course be debated but I would say it is usually a logic/rule. For example a business rule may be that if an `Order` is deleted all `OrderItems` should be automatically deleted. But it could also be that it should never be possible to delete an `Order` if it still have `OrderItems`. So this may be up to the business to decide. How do we know how this rule is currently implemented? If it is all in code we can just look at the code (high cohesion). If the rule is maybe implemented in the code or maybe implemented as cascade in the database then we need to look in multiple places (low cohesion).

Of course if you go all-in with putting your business rules only in the database and use triggers, stored proc then cascade may make sense.

I usually consider database vendor lock-in before using any stored proc or triggers. A SQL database that just stores data and enforces integrity is IMO easier to port to another vendor. So for that reason I usually don't use stored proc or triggers.