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?

  • I'm looking for use-cases, and I don't think they're database-specific (might be wrong here) Aug 29, 2011 at 15:51
  • I have never seen a use-case for this either...
    – user1822
    Aug 29, 2011 at 16:23

2 Answers 2


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

  • Good points with a strict meaning of null, and with your example it's clear why this is a feature. Aug 30, 2011 at 13:24

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.

  • 3
    In this case I would argue not to allow deletes (RESTRICT), if for no other reason that to point the blame to an old employee when something goes wrong with it! Aug 29, 2011 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.