Are primary keys passé?

Question

What unique functionality do Primary Keys provide?

While i titled the question with tongue firmly planted in cheek, my question is serious. Before any flames start, I'm not saying build a database without constraints or referential integrity. As far I can tell, however, SQL Server could do away with the primary key key word.

• Unique indexes cover, well, uniqueness
• Column based Non-nullability covers the non-nullability requirement for PKs
• PK's don't have to be clustered, so that's not it
• Foreign keys can, and often are, implemented with unique indexes, rather than PKs
• Even MSDN states that a unique index is created to enforce the PK's uniqueness

I do agree that logically a Primary Key coveys a bit of intention about a data model, but is that it? [sarcasm]Oh, and we do get that little Key icon SSMS shows when designing a table! [/sarcasm]

---

EDIT

From the comments, it seems clear I didn't ask this question as clearly as I thought. I agree that primary keys are important from a logical perspective.

I'm not asking:

• should i choose an int or a varchar for my PK
• do PK's have to be clustered, or how do i identify what should be clustered
• how do i uniquely identify rows

My intention was to ask "what features do PK's provide that cannot reasonably be implemented using other features?" I'm not suggesting going crazy here -- like using a trigger to enforce uniqueness instead of unique constraints/indexes. Reasonable is a key word here -- and using a unique index/constraint seems very similar to defining a PK.

Answer 1

A completely different perspective :

SQL is a language that is defined by an ISO standard. That standard has "mandatory" features and "optional conformance" features.

If you build a DBMS with some data manipulation language, then you are entitled to call your language "SQL" only if :

(a) you have implemented ALL of the syntax prescribed by the standard ("mandatory" features) , and (b) all of the language features that you have implemented (all the mandatory ones as a minimum, but also the "optional" ones you "opted in" for), expose exactly the behaviour as defined/Described in the standard.

The "PRIMARY KEY" syntax is a very old feature, and it's not unlikely that it is one of those "mandatory" ones. Ditching the word from your language means you can no longer legitimately call your language SQL. Big commercial vendors are not likely going to make such a move any time soon.

Answer 2

The idea of designating one key per table as a "primary" one is essentially superfluous, outdated and in many ways very unhelpful.

It is superfluous because logically speaking all keys can and do serve the same function. Leaving aside the limitations of any particular DBMS, logically speaking a "primary" key enjoys exactly the same features and functionality as any other key of the same table. The designation of one key as "primary" is therefore only as important as the database designer or user wants it to be. The distinction is arbitrary (that's the word used by E.F.Codd) and purely psychological (C.J.Date).

The concept is outdated because in modern practice it is commonplace for tables to have more than one key and for different users and consumers of data to have different "preferred" or "most significant" identifiers for the same piece of data. E.g.: an end user may recognise and use one key of a table (often the one called a "business" or "natural" key); a middle-tier programmer will possibly be more interested in a different key in the same table (e.g. a "surrogate" key); the DBA on the other hand may view the "clustered" key as the most important or maybe he is equally concerned with all keys that have indexes. So the preferred or most important key depends on the point-of-view and the intended usage - it is not a rigid structural feature at all.

The "primary key" concept is unhelpful for at least two reasons. Firstly, software vendors of database development tools, DBMSs and modelling tools have unfortunately attached all sorts of software features to the keys designated as "primary key". This actually works against the original concept. No longer do we just need to select one key per table that has some logical significance for the designer or user. We are encouraged or even compelled to choose "primary" keys to support this or that feature in X,Y or Z piece of software, regardless of other considerations. This is very regrettable because it represents a limitation and a lack of flexibility in software. We ought to be free to choose an appropriate key for each purpose and not be restricted to just one key per table for every purpose.

The final reason that primary keys are unhelpful is that they are a needless distraction from more important issues of database design. The primary key concept is given often vastly exaggerated significance in education, in textbooks on database design and in everyday data management practice. This is frequently to the detriment or actual exclusion of the more fundamental issue, i.e. that all of the keys and all of the other integrity constraints can be just as important to successful database design and implementation.

I have often argued that the term "primary key" ought to be deprecated and dropped from data management vocabulary as well as from data management software.