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

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?

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

• Choose (and document) what it means for each nullable field

• 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

StackExchange Database Administrators Q#5176, answer score: 13