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?
foreign keynull
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?
Best Answer
Whether
set null
is useful or not depends on what you have chosennull
to mean in the particular context - with all the confusion and opinion aroundnull
IMO the sensible approach is for the DBA toWith those rules, consider the following use case:
null
to 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