sql server - 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.