sqlvogel (2/11/2014)

---

Jeff Moden (1/30/2014)

---

By definition and by design and as the others have stated, you cannot have two Primary Keys on a table. If you try to create a second real Primary Key on a table, it will give you an error

By definition? Well perhaps yes but not necessarily. Let's not confuse the concept of a primary key with the misnamed construction called a PRIMARY KEY constraint in SQL. The set of columns subject to a PRIMARY KEY constraint is not necessarily the actual "primary key" of a table. So the fact that SQL Server has a limitation that only allows the PRIMARY KEY syntax to be used once per table isn't necessarily the definite answer to the question. Anyone who thought to point that out in an interview would certainly get extra points from me.

In principle any candidate key of a table can be called "primary", meaning it is designated as the "preferred" identifier for information in that table. Since the primary key is not fundamentally different to any other key it isn't any great matter of principle that there must only be one such key. Historically (before SQL was commonplace), the term "primary key" was used for any and all keys of a table and not just one. For ease of comprehension and to simplify some aspects of design and implementation it frequently makes sense to designate exactly one primary key but there's no absolute need to be bound by that rule if you find an exceptional reason to do otherwise.

My favourite example, a table of Marriages with exactly two attributes: Husband and Wife. Both attributes are candidate keys because clearly we don't want to allow the same person to have more than one marriage simultaneously. So which key is "primary", husband or wife? Does it make any real difference if I pick either or even both of those as primary keys?