-1

I am learning SQL and particularly progressing at Foreign Keys. I understand the difference between set NULL vs set CASCADE, but not yet clear on the use cases for them.

When do you set NULL and when do you set CASCADE for a foreign key?

Quan Bui
  • 175
  • 1
  • 13
  • 1
    Have you seen a reply tweet to another tweet that have been deleted? If the developers of twitter had set the "reply_from_id" field of "tweets" table to CASCADE, all replies would be deleted when someone deleted their tweet – Bad Dobby Sep 04 '21 at 15:54
  • 1
    It is another good practice to NOT set constraint for foreign keys. For example, Github developers do not use foreign keys. https://github.com/github/gh-ost/issues/331#issuecomment-266027731 – Bad Dobby Sep 04 '21 at 15:58
  • Thanks for the concrete example, it really helps me understand the different purposes of the 2 settings. – Quan Bui Sep 04 '21 at 16:03

2 Answers2

2

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.

Serg
  • 22,285
  • 5
  • 21
  • 48
1

There are three modes to consider.

In set NULL, the foreign key will notice the deleting of the parent row. Then the foreign key will orphan the child rows by updating the ParentId on each child row to null.

In set CASCADE, the foreign key will notice the deleting of the parent row. Then the foreign key will delete the child rows.

The main consideration between selecting these two modes, is whether parent-less child rows are permitted.

In an enforced foreign key (the default behavior), the foreign key will notice the deleting of the parent row and notice that there are child rows. Then the foreign key will rollback the transaction, restoring the parent row.

The main consideration between selecting this mode and the other two, is whether the database or the user should handle resolving deletes that violate the foreign key. By rolling back the transaction the database prevents accidental deletes/updates of the child rows. This allows the user to evaluate/change their request.

Amy B
  • 108,202
  • 21
  • 135
  • 185
  • Thanks for your answer Amy. However, for me, it seems like CASCADE is just always the better option? I still don't see at which case, would set NULL (allowing orphan row) would be a better option? – Quan Bui Sep 04 '21 at 15:52
  • 1
    Suppose you have an Employee table, with a foreign key SupervisorId, child of EmployeeId same table. If the director steps down and we delete their row, then all the employees are deleted. – Amy B Sep 04 '21 at 16:01