Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
I'm looking for use-cases, and I don't think they're database-specific (might be wrong here) –  Derek Downey Aug 29 '11 at 15:51
    
I have never seen a use-case for this either... –  a_horse_with_no_name Aug 29 '11 at 16:23
add comment

2 Answers

up vote 6 down vote accepted

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

share|improve this answer
    
Good points with a strict meaning of null, and with your example it's clear why this is a feature. –  Derek Downey Aug 30 '11 at 13:24
add comment

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.

share|improve this answer
2  
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! –  Derek Downey Aug 29 '11 at 17:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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