Why can a database table have only one primary key?

Question

A database table can only have one primary key not two or more .. why is that so?

Answer 1

Because a primary key is used to determine a unique row in a table.

This means that every other column in a given row should be dependent on the primary key column. If a table had multiple primary keys it would imply that the primary key fields were capable of determining each other along with the rest of the fields in the table, which would in turn imply issues with how the data was being stored and the schema design of the database.

Answer 2

I know this is closed but...

ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY (C1, C2, C3)

You can have a PK with one, two, three .... combinations.

Answer 3

To identify a particular row/tuple using a single unique value.

Answer 4

A (relational) table's "superkeys" are the sets of columns for which each row has a subrow unique in the table. (Note that every superset of a superkey is a superkey too.) (What unadorned SQL KEY declares, and supersets of those.) A superkey that contains no smaller superkey is a "candidate key". Normalization and other relational theory cares about candidate keys and does not care about primary keys. As far as the meanings of queries, updates and constraints go, there is no need or basis for choosing one candidate key and calling it "primary" (and the others "alternate"). It's just a tradition carried over from pre-relational systems from the early days of the relational model when it wasn't understood to be unnecessary.

It isn't necessary for purposes of indexing either (which has to with performance, another important observable of expressions).

Then, because there was a tradition of having primary keys, other things (like automatic indexing) got attached to them. But those things didn't need to be attached to primary keys, and primary keys are not necessary for those other things.

SQL only lets you declare one PRIMARY KEY, because there's only "supposed" to be one primary key, but that doesn't mean there's a good reason to declare any outside of the attached functionality. Anyway, SQL PRIMARY KEY actually means UNIQUE NOT NULL, ie superkey, not candidate key, so only if no UNIQUE NOT NULL is declared on a proper subset of a PRIMARY KEY's columns is it declaring a primary key. So the fact that SQL PRIMARY KEYs aren't necessarily primary keys shows how empty that claimed need for primary keys is. (And SQL FOREIGN KEYs aren't foreign keys, because they don't reference any but only candidate keys (as they should), or even any but only primary keys, or even any but only PRIMARY KEYs, they reference any but only superkeys. So again, such claims for the necessity of primary keys are empty.)

Most SQL DBMSs automatically and specially index PRIMARY KEYs. But that's just a certain way of exposing to the user certain ways of implementing.

It is sometimes claimed that having a single way of referring to core business entities justifies having base table primary keys. However, any superkey of any table expression, ie any superset of any candidate key of one, identifies everything that any contained superkey does (including a primary key). So even if an entity's primary key columns are absent, a query can still have columns identifying it. Moreover, any superkey of any table expression identifies some entity whether or not it is identified in some base table (let alone by a primary key). Moreover, even if columns are projected/SELECTed away by a query, the meaning of its rows is still in terms of the meaning of the tables that held those columns. So again a query, update or constraint can involve a core business entity without its designated primary key columns being present. And it can involve derived entities without associated base primary key columns. So claims that primary keys are needed or fundamental for or to uniquely identifying are unfounded.

Answer 5

In relational database model, primary key is the identifying attribute to guarantee unique access. When you specify primary key for a table, the Database Engine enforces data to be unique by creating a unique index for the primary key columns.

Further reading in MS Technet: https://technet.microsoft.com/en-us/library/ms191236(v=sql.105).aspx

Answer 6

The major reason is because that is the definition of the primary key. A table can have multiple unique keys that identify each row, but only one primary key.

In databases such as MySQL, the primary key is also a clustered index. That provides a more direct reason. The data is sorted on the pages according to the clustered index. A table can only have one sort order.