What is the purpose of SET NULL in Delete/Update Foreign Keys constraints?

Question

I am probably being narrow minded, but if I create a foreign key constraint and a row gets updated or deleted, I lose that connection if the child table's column gets set to NULL.

What is the purpose of intentionally keeping these orphaned rows?

Answer 1

Whether set null is useful or not depends on what you have chosen null to mean in the particular context - with all the confusion and opinion around null IMO the sensible approach is for the DBA to

1. Choose (and document) what it means for each nullable field
2. Make sure it means one thing only

With those rules, consider the following use case:

• You have a table 'shop' (eg individual premises)
• You have a table 'retailer' (eg chains)
• The 'shop' table has a lookup field referring to the key of 'retailer'
• You have defined null to represent an independent shop (ie one that is not part of a chain)
• A 'retailer' closes branches to the point that you consider its shops to be independent

In this case, an on delete set null makes sense. There are other ways of modelling these business rules but this is the simplest and if it accurately fits the facts that you care about in the real world, I suggest it is perfectly ok

Answer 2

Most of the use cases I can think of generally are of the form "Well, we could do something different but we felt like doing this instead".

Consider an owner field for a bug in an issue tracking system at a company. If John quits, surely all his problems should not simply be deleted when his account is removed. Even then, some databases allow SET DEFAULT as an action (or having a trigger perform the same task), and having a Nobody employee be the default owner of issues could be an option. Or we could have a disabled field for John rather than deleting his record.