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?
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 only takes a minute to sign up.Sign up to join this community
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
With those rules, consider the following use case:
nullto represent an independent shop (ie one that is not part of a chain)
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
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".
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.