Can a database table contains more than one primary key?

The primary key of a relational table uniquely identifies each record in the table. So, in order to keep the uniqueness of each record, you cant have more than one primary key for the table. It can either be a normal attribute that is guaranteed to be unique (such as Social Security Number in a table with no more than one record per person) or it can be generated by the DBMS (such as a globally unique identifier, or GUID, in Microsoft SQL Server).

Primary keys may consist of a single attribute or multiple attributes in combination.

2 Social security number is a terrible primary key! – Steven Schlansker Jan 6 '10 at 7:32.

A table can have: No primary keys; One primary key consisting of one column; or One composite primary key consisting of two or more columns. Other than that you can have any number of unique indexes, which will do basically the same thing.

That's why it is called Primary Key because it is, well, PRIMARY.

That doesn't help the original poster in explaining why it's primary. – arabian tiger Jan 6 '10 at 6:41.

Yes, you can have Composite primary keys, that is, having two fields as a primary key.

This is not really the same as having two primary keys: It is still only one primary key. – Daren Thomas Jan 6 '10 at 7:45 agreed, but you can achieve the same functionality(i. E have 2 primary keys in other way) that is, if you apply all the primary key rules to another field, like not null and unique, the only key point left would be is to refer it as a foreign key that you can do manually, by having your custom joins.

– Ali Jan 7 '10 at 6:33.

The short answer is yes. A primary key is a candidate key and is in principle no different to any other candidate key. It is a widely observed convention that one candidate key per table is designated as the "primary" one - meaning that it is "preferred" or has some special meaning for the database designer or user.

This is just convention however. It is only a label of convenience and a reminder about the potential significance of one key. In practice all keys can serve the same purpose and the "primary" one is not special or unique in any fundamental way.

First of all, you have to understand the history of entity-relationship design methodology as well as understand the word "relational" in relational database management systems (RDBMS). " May I suggest politely that you first get YOURSELF educated on these very same subjects before leading other people into flawed beliefs? I'll respond to the two worst ones of your stupidities below."According to relational methodology principles, each entity should only have one and only one means to identify it.

" That is about the biggest crap I have ever heard anybody spawn around about relational data design. The relational model does not constrain any "entity", as you erroneously call it, to have any precise number of keys. Any "entity" can have any number of keys, and EACH key is, by definition of its very property of making the "rows" unique, a valid candidate for any purpose of "identification".

Choosing the most useful/appropriate one for use in certain contexts (foreign keys in referencing tables, e.g. ), is a design issue, and the relational model does not have anything to say on such things."Therefore, "R"DBMS attempts to facilitate the modeling of entity relationships. " Codd's paper "A Relational model of date for large shared data banks", which marks the birth of the relational model, predates the invention of E-R by a number of years. So to say that the Relational model attempts to facilitate the modeling of E-R concepts, is having things COMPLETELY backwards, and nothing but a display of one's own complete and utter ignorance of "the history" that you referred to in your own answer.

Thanks for the admonishment. Thanks for the honour of your registering just to admonish me. However, ERD and R is too tightly bound together to say that that they don't constrain each other nowadays.

– Blessed Geek Jan 14 '10 at 0:03.

First of all, you have to understand the history of entity-relationship design methodology as well as understand the word "relational" in relational database management systems (RDBMS). In order to define the bounds of an entity and relationships to be formed, there must be a unique handle or a unique combination of handles to identify each single instance of an entity and then to form relationships between them. You also need to understand the meaning/root of the word "identify" which is to zero in on the "identity" of each instance of an entity."identity" being the mathematical term meaning "one" or a singularity.

According to relational methodology principles, each entity should only have one and only one means to identify it. Therefore, "R"DBMS attempts to facilitate the modeling of entity relationships. Note the differences between "Entity/Class" and "Entity/Class instance".

However, RDBMS is used widely and mostly by people not so interested in accurately portraying the E-R design principles. So that frequently, we have more than one possible entity-definition sitting inside a table, which I call entity-aliasing. Opposed to identity-aliasing, where two or more instances of an entity-set hides under the same key, entity-aliasing is like the table EmpProj(empId, empName, empAddr, projId, projLoc) actually has two entity-sets aliased under the same table: Emp(empId, empName, empAddr) Proj(projId, projLoc, empId) That is when normalisation comes in - to separate these entities out.

Try as we might to do a decent design normalisation, computer scientists may not have as good a perspective on the information as a statistician. The computer scientist (which in this discussion includes everyone with a decent knowledge of ER design) tries his/her best in creating a schema that cleanly defines entities and their relationships. However, after 18 months analysing voluminous information from the database, the statistician begin to see principal components that emerge whose analyses is terribly crippled due to the misalignment of the principal components with those of boundaries of the computer scientists' perceived entities.

That is where alternate unique keys are good for - to identify instances of entities due to the principal components existing as ghost-entities in the database. Therefore, the primary key of a table is because that table is perceived to be a perfect entity as an entity should have only one primary key, be it singular or composite.As far as the statistician is concerned, even though the database allows only one primary key per table, the alternative unique keys is to the statistician the primary keys to those ghost-entities. Which is why sometimes you are frustrated by statisticians who seem to do double work by downloading the data into the local database of their workstation/PC.

In conclusion, the constraint placed by the "R"DBMS manufacturer in allowing only one primary key per table is their pretense in believing that they know how information behave and believing that principal components of the information due to the population do not mutate over time. If you have more than one unique keys possible in a table it means either one or more of the possibilities Like myself, you are lazy to separate them since they seem to work quite well For performance' sake, mixing the entities into the same table makes the application run incredibly faster Like the statistician, you gradually discover ghost entities in your information.

According to relational methodology principles, each entity should only have one and only one means to identify it" This is nonsense. The relational model requires that each tuple be identifiable by AT LEAST ONE key. There is no requirement that there should be ONLY one key or that one key is in any way special.

– dportas Jan 25 at 10:08.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.