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

Question

I know that in the table there can be no more than one primary key, but what is the technical reason?

Answer 1

Executed directly from fooobar.com/questions/24794 / ... :

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

You can also have unique indexes in your table that will look a bit like a primary key, as they will use unique values ​​and speed up the query for these values.

Primary in the context of the Primary key means that it takes first place in importance. Therefore, there can be only one key. This is by definition.

It is also usually the key for which the index has actual data associated with it, i.e. the data is stored with the primary key index. Other indexes contain only data that is indexed, and possibly some Included Columns.

Answer 2

In fact, EFCodd (inventor of the relational database model) [1] coined the term "primary key" to refer to any number of relationship keys, not just one. He made it clear that it is possible to have more than one such key. His suggestion was that the database designer could choose one key as the preferred identifier ("primary key"), but in principle it was optional, and such a choice was "arbitrary" (that was his word). Since all keys have the same properties as each other, there is no fundamental need to choose any other over another.

Later [2] what Codd originally called primary keys became known as candidate keys, and one key was highlighted as the preferred one, which became known as the "primary" key. However, this was not a fundamental shift, since the primary key means the same as the candidate key. Since they are equivalent, this does not mean anything important when we say that "should" be only one primary key. If you have more than one candidate key, you may well call more than one of them “primary” if you want, because it does not make a logical or practical difference in the value and function of the database.

It was suggested (among me, among other things) that the idea of ​​designating one key for each table as “primary” is completely redundant, and sometimes a positive obstacle to a good understanding of database and data design problems. However, the concept is so ingrained that we are probably stuck with it.

So the correct answer to your question is “agreement” and “convenience”. There is no good technical reason.

[1] Relational data model for large common data banks (1970)

[2]. in "Further normalization of the relational database model" (1971)

Answer 3

PRIMARY KEY usually equivalent to UNIQUE INDEX NOT NULL . Thus, you can effectively have multiple "primary keys" on the same table.

Answer 4

Well, this is called "primary" for some reason. As in the case of one key used to uniquely identify the record ... and there " can be only one ".

You could, of course, transfer the second “primary” key by specifying one or more other fields that are unique, but for the purposes of your database server this is generally necessary only if your key is not unique enough for the cross-database servers in a merge replication situation. (i.e.: multi master).

Answer 5

A primary key is a key that uniquely identifies this entry.

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

The first is possibly known as the composite primary key.

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

Answer 6

Since the "primary" in the "primary key" denotes it, mmm, feature (?).

But if you need more, you can define UNIQUE keys that have exactly the same behavior.

Answer 7

The technical reason is that there can be only one primary . Otherwise, this would not have happened like that.

However, the primary key may contain several columns - see 7.5.2. Multiple Column Indices

Answer 8

A primary key is one (possibly many) unique identifiers for a particular row in a table. Other unique identifiers that were not designated as primary, therefore, often refer to secondary unique indexes.

Answer 9

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

Answer 10

The primary key determines the uniqueness of the record. It may be problematic to have two different measures of uniqueness. For example, if you have primary keys A and B, and you insert records where A is the same and B is different, are these records the same or different? If you consider them different, then make your primary compound of A and B. If you consider them the same entry, then just use A or B. As the primary key.

Answer 11

• For a non-clustered index, we can create two indexes and usually create in non-primary key columns used in JOIN, WHERE, ORDER BY clauses.
• So far in the cluster index we have only one index, and on the primary key. Therefore, if we have two primary keys, there is ambiguity.
• Also in referential integrity, there is an ambiguity that selects one of two primary keys.

Answer 12

Only one primary key can be used in the table, since the primary key creates a clustered index in the table, which physically stores the data on the node sheet in an orderly manner based on this primary key column. If we try to create one other primary key in this table, then there will be one serious data problem. Because it cannot store the same table data in two different orders.