7

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

KarSho
  • 5,699
  • 13
  • 45
  • 78
Vishwanath Dalvi
  • 35,388
  • 41
  • 123
  • 155
  • 7
    Well, it would no longer be called a "primary" key if there was more than one. What would the benefit be of having more than one primary key anyway? – Evan Mulawski Aug 19 '11 at 19:22
  • 3
    Imagine a class full of school children. Teacher: "Peter, did you do your homework?" Peter Smith: "Yes" Peter Miller: "No" Peter Jones: "Yes" Teacher: "Peter Miller, did you do your homework?" Peter Miller: "No" – Johannes Fahrenkrug Feb 08 '13 at 08:16

12 Answers12

14

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.

Community
  • 1
  • 1
Taryn
  • 242,637
  • 56
  • 362
  • 405
12

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)

nvogel
  • 24,981
  • 1
  • 44
  • 82
5

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).

NotMe
  • 87,343
  • 27
  • 171
  • 245
4

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

Lucas
  • 8,035
  • 2
  • 32
  • 45
  • 1
    Close but no cigar. If a table has several primary key candidates, each column's contents (row) are not duplicated and any 'could be selected' as a primary key. AFAIK, The only real reason that ONE primary key can be defined is because, MS amongst others, will use the defined primary key to create a CLUSTERED index ... the table is physically sorted by the primary key. CONSTRAINT [PK_Table_Column] PRIMARY KEY CLUSTERED. Other candidates should use your UNIQUE INDEX constraint to guarantee integrity. Your code should then use the defined primary key for look-ups for efficiency. – IdahoB Mar 17 '21 at 18:01
4

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.

stwalkerster
  • 1,646
  • 1
  • 20
  • 30
3

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.

glglgl
  • 89,107
  • 13
  • 149
  • 217
3

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

Saul
  • 17,973
  • 8
  • 64
  • 88
2

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.

Janick Bernet
  • 20,544
  • 2
  • 29
  • 55
1

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."

Vinay
  • 1,016
  • 8
  • 22
  • 1
    "A primary key can have multiple columns" and "you can have two primary keys in a table" are two very different things. The first one is true; the second is false, whether you're looking at it from a relational point of view or from a SQL point of view. – Mike Sherrill 'Cat Recall' Aug 21 '11 at 01:54
1

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.

brandx
  • 1,053
  • 12
  • 10
  • 2
    A primary key is one way to uniquely identify rows. It's not the only way, regardless of whether you take a relational or a SQL point of view. And it's actually common for tables to have multiple candidate keys. – Mike Sherrill 'Cat Recall' Aug 21 '11 at 01:50
0
  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.
Suyog411
  • 1
-2

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.

RajeshVerma
  • 1,087
  • 9
  • 13
  • 1
    A primary key creates a clustered index by default (if there isn't already one on the table), but you can force that index to be non-clustered upon creation of the primary key with the keyword NONCLUSTERED. If there is already a clustered index, then the primary key will create by default a non-clustered index. No problem related to the data as the primary key and the index serve different purposes. – Guillaume Sep 13 '17 at 05:46