Does anybody know any cases/example that we need to use UNIQUE KEY instead of PRIMARY KEY? I understand that UNIQUE KEY can allow one NULL value in the data, but I don't know the reason for that. Why do we need that extra NULL VALUE? And, When do we need it?
A Primary Key is a Referential Integrity constraint, that's why it should never allow a Null value.
On the other hand, an Unique Index is an index that doesn't allow for duplicate values but it is not a Referential Integrity constraint.
In my opinion, you do not "need" and extra Null Value - it is just allowed to be there even if I would never ever allow for Null Values on a Unique Index, it is not elegant and a symptom that something is wrong with the way data was modeled.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at Amazon and other bookstores.Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.