February 11, 2014 at 4:03 pm
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 errorBy 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?
All true but, as you have pointed out yourself, there is only one Primary Key in T-SQL. You can have a dozen different alternate keys, if you'd like, but, by definition in T_SQL, there can only be one "Primary Key" on any given table.
--Jeff Moden
February 11, 2014 at 4:25 pm
sqlvogel (2/11/2014)
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.
That's the point. A table can have multiple candidate keys but just one primary key.
February 12, 2014 at 9:11 am
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?
It would certainly seem to make a real difference. If the husband is the primary key, wives could still have multiple husbands, and vice versa. This would be a tricky one and subject to obvious questions like is the record deleted upon end of the marriage. If not, people remarry. As I don't ever have to expect to maintain such a table, that's as far as I will take these thoughts.
I would chime in, however, that I agree that the original post could have included some thoughts on the matter, newness not withstanding.
February 12, 2014 at 10:16 am
RonKyle (2/12/2014)
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?
It would certainly seem to make a real difference. If the husband is the primary key, wives could still have multiple husbands, and vice versa.
No because I already stated that both Husband and Wife are keys (i.e. candidate keys). Enforcing all the desired keys of a table is of course extremely important - much more important than choosing one of them to be "primary". The point of the example is that it doesn't make any difference which key you call primary. It shouldn't even make any difference if you call both of them primary. Whatever distinction you choose to ascribe to the primary key could in principle apply equally well to any or all other keys so the form, function and intended meaning of the table isn't altered by that essentially arbitrary choice.
February 12, 2014 at 10:43 am
That's the difference of unique keys and primary keys. Primary keys will idenitfy a row within a table and they're essentially a preferred unique key. Unique keys will enforce uniqueness and a table can have as many as needed.
For me, the primary key on the marriages table should include both columns (husband & wife) or I might choose a surrogate key if the situation is correct for that. Husband and wife might be both foreign keys to a table People (and the PK for that table is a great discussion on its own).
February 12, 2014 at 10:59 am
Luis Cazares (2/12/2014)
That's the difference of unique keys and primary keys. Primary keys will idenitfy a row within a table and they're essentially a preferred unique key. Unique keys will enforce uniqueness and a table can have as many as needed.
Any key identifies a row in a table. That's what we mean by a key. Actually any superkey identifies a row in a table. A key is just the minimal set of attributes in any superkey.
For me, the primary key on the marriages table should include both columns (husband & wife) or I might choose a surrogate key if the situation is correct for that.
But the requirement is that both Husband and Wife columns be unique (current, monogamous marriages only are permitted). The composite of Husband and Wife cannot be a primary key if Husband and Wife are both candidate keys because a key has to be irreducible - i.e. the smallest subset of attributes that form a superkey. It's true that in SQL Server you could create a PRIMARY KEY constraint on (Husband,Wife) while still having UNIQUE constraints on (Husband) and (Wife) individually but that's just a syntax peculiarity of SQL. The constraint named "PRIMARY KEY" in that case would not be a primary key constraint at all, it would just become a (mostly redundant) superkey constraint.
Adding a surrogate doesn't really alter things. That just adds a third key to the picture without answering the essential dilemma of whether and why one key needs to be preferred over any other.
February 12, 2014 at 11:15 am
No because I already stated that both Husband and Wife are keys (i.e. candidate keys). Enforcing all the desired keys of a table is of course extremely important
The second sentence above was not in your original post, and answers my comment. Thanks for the clarification.
February 12, 2014 at 12:25 pm
In the world of BI it is very common in dimensional design to know both the physical primary key of a target dimension table and the logical equivalent business key. With dimensions being denormailzed quite often, there maybe be a primary key from more than one source table that contributes to a composite logical primary key in the target dimension--this is also known as the alternate key as Jeff has pointed out.
Hope it helps.....
February 20, 2014 at 3:11 am
Actually, when we define primary key on a table, data is stored in the physical order of primary key, We can't have two physical order in a table. This is the reason that we can't have two primary keys in a same table. Thanks
February 20, 2014 at 3:12 am
azhar.iqbal499 (2/20/2014)
Actually, when we define primary key on a table, data is stored in the physical order of primary key
Not true.
The clustered index (not primary key) enforces the logical storage order of the data. It does not enforce the physical storage order however.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 20, 2014 at 12:48 pm
azhar.iqbal499 (2/20/2014)
Actually, when we define primary key on a table, data is stored in the physical order of primary key, We can't have two physical order in a table. This is the reason that we can't have two primary keys in a same table. Thanks
Primary key has nothing to do with the physical order data is stored in. Keys are a logical construct not a physical one.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply