Search code examples
sqldatabaseforeign-keysconstraintscascade

When to SET NULL vs CASCADE constraints on a foreign key in SQL?

I understand the fundamental differences between SET NULL vs. CASCADE on a foreign key, but not yet clear on WHEN should one favor one more than the other.

When do you set CASCADE?

Solution

  • Generally we prefer ON DELETE SET NULL when the lifecycle of the parent is to be shorter then the lifecycle of the child.

    For example consider a delivery system where each Parcel is assigned to exactly one Route/Trip. So there exists FK Parcel.RouteId to Route.Id. A system user can create a Route, assign some Parcels to it (set Parcel.RouteId). A bit later the user decides to delete a Route for a reason. Definetly we don't want Parcels in the Route to be deleted too. So we specify ON DELETE SET NULL for this FK.