Question: Is there value in denoting a key as Primary?This question is not about the definition of a primary key. It is not about the fact primary's aren't nullable. It is not about implementations such as MySQL which automatically perform clustered index voodoo behind the scenes on primary keys.NOTE: The question is not about the benefits of auto-number-ID surrogates over natural keys.Suppose a table has 3 candidate keys. All non-nullable. All immutable. I find no value denoting a key as secondary or tertiary. I don't find the concept of precedence useful when dealing with multiple keys. I find the same logic against secondary and tertiary flags applies to the primary flag.I even feel the word candidate is not useful for a key. It implies the candidate is not as important as the key that won the primary election. I don't feel precedence has any place in describing keys. The attributes of the key itself (unique, non-nullable, etc) should be sufficient.Would the relational model be missing anything if we did not have the primary attribute to describe 1 of a table's keys?
Is there value denoting a key as Primary?
relational database
Logically and practically speaking there are really no primary keys in the relational model [1] because all candidate keys can and do have exactly the same status, features and function in the relational model: candidate keys are irreducibly unique, non-nullable identifiers for tuples in a relation.The practice of singling out any one key as preferred, most important or for any other purpose is a matter of convention and convenience rather than necessity. If a relation has only one candidate key then we might just as well call it a primary key by default. If a relation has more than one key then the choice of which to call primary is only as important as you want it to be and the significance of that choice is defined by the user and/or by the syntax and DBMS features used to define it.You are certainly not alone in finding the idea of designating a primary key of somewhat limited and dubious value. Especially so in the database systems of today, compared to those of decades past. The primary key assumption is a controversial notion that is arguably outdated and by rights probably ought to be retired by now. Unfortunately it's an assumption that is heavily entrenched in the mindset and practices of many database practitioners.[1] E.F.Codd originally used the term primary key to mean any and all keys of a relation and stated that a relation could have more than one such key. It was only later that the term candidate key appeared in the relational database literature and the term primary key took on its modern meaning of denoting one and only one key per relation.