Why we can't have more than one primary key?

Question

I Know there can't be more than 1 primary key in a table but what is the technical reason ?

Answer 1

Only one primary key possible on the table because primary key creates a clustered index on the table which stored data physically on the leaf node in ordered way based on that primary key column. If we try to create one another primary key on that table then there will be one major problem related to the data.Because be can not store same data of the table in two different-2 order.

Answer 2

1. For non-clustered index we can create two index and are typically made on non-primary key columns used in JOIN, WHERE , ORDER BY clauses.
2. While in clustered index we have only one index and that on primary key. So if we have two primary keys there is ambiguity.
3. Also in referential intergrity there is ambiguity selecting one of the two primary keys.

Answer 3

In fact E.F.Codd (the inventor of the Relational Database Model) [1] originated the term "primary key" to mean any number of keys of a relation - not just one. He made it clear that it was quite possible to have more than one such key. His suggestion was that the database designer could choose one key as a preferred identifier ("the primary key") - but in principle this was optional and such a choice was "arbitrary" (that was his word). Because all keys enjoy the same properties as each other there is no fundamental need to choose any one over another.

Later on [2] what Codd originally called primary keys became known as candidate keys and the one key singled out as the preferred one became known as the "primary" key. This was not really a fundamental shift however because a primary key means exactly the same as a candidate key. Since they are equivalent concepts it doesn't really mean anything important when we say there "must" only be one primary key. If you have more than one candidate key you could quite reasonably call more than one of them "primary" if you prefer because it doesn't make any logical or practical difference to the meaning and function of the database.

It has been argued (by me among others) that the idea of designating one key per table as "primary" is utterly superfluous and sometimes a positive hinderance to a good understanding of database design and data intgrity issues. However, the concept is so entrenched we are probably stuck with it.

So the proper answer to your question is "convention" and "convenience". There is no good technical reason at all.

[1] A Relational Model of Data for Large Shared Data Banks (1970)

[2] E.g. in "Further Normalization of the Relational Data Base Model" (1971)

Answer 4

A primary key defines record uniqueness. To have two different measures of uniqueness can be problematic. For example, if you have primary keys A and B and you insert records where A is the same and B is different, then are those records the same or different? If you consider them different, then make your primary a composite of A and B. If you consider them the same record, then just use A or B as the primary key.

Answer 5

Pulled directly from SO:

You can only have one primary key, but you can have multiple columns in your primary key.

You can also have Unique Indexes on your table, which will work a bit like a primary key in that they will enforce unique values, and will speed up querying of those values.

Primary in the context of Primary Key means that it's ranked first in importance. Therefore, there can only be one key. It's by definition.

It's also usually the key for which the index has the actual data attached to it, that is, the data is stored with the primary key index. Other indices contain only the data that's being indexed, and perhaps some Included Columns.

Answer 6

The primary key is the one (of possibly many) unique identifiers of a particular row in a table. The other unique identifiers, which were not designated as the primary one, are hence often refereed to as secondary unique indexes.

Answer 7

Primary key allows us to uniquely identify each record in the table. You can have 2 primary keys in a table but they are called Composite Primary Keys. "When you define more than one column as your primary key on a table, it is called a composite primary key."

Answer 8

The primary key is the key which uniquely identifies that record.

I'm not sure if you're asking if a) there can be a single primary key spanning multiple columns, or b) if you can have multiple keys which uniquely identify the record.

The first is possible, known as a composite primary key.

The second is possible also, but only one is called the primary key.

Answer 9

The technical reason is that there can be only one primary. Otherwise it wouldn't be called so.

However a primary key can include several columns - see 7.5.2. Multiple-Column Indexes

Answer 10

Because the "primary" in "primary key" denotes its, mmm, singularity(?).

But if you need more, you can define UNIQUE keys which have quite the same behaviour.

Answer 11

Well, it's called "primary" for a reason. As in, its the one key used to uniquely identify the record... and there "can be only one".

You could certainly mimick a second "primary" key by having an index placed on one or more other fields that are unique but for the purposes of your database server it's generally only necessary if your key isn't unique enough to cross database servers in a merge replication situation. (ie: multi master).

Answer 12

PRIMARY KEY is usually equivalent to UNIQUE INDEX NOT NULL. So you can effectively have multiple "primary keys" on a single table.