0

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

Pankaj katiyar
  • 464
  • 10
  • 26

6 Answers6

8

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    nicely put. The primary order of the table in its physical layout is the main practical reason for only ONE primary key I think. – luksch Jul 17 '15 at 12:46
  • @luksch . . . However, that is not true in all databases. SQL Server allows clustered indexes on non-primary keys. – Gordon Linoff Jul 17 '15 at 19:05
  • Re your 1st sentence: That is not a reason why, that is just two restatements of the fact. (Belying the fact that there is no necessity hence no reason why.) Re your 2nd statement: Since the primary key is not always a clustered index, clearly that is not a reason why one must have a primary key. (Even if primary keys were necessary for having clustered indexes, which of course they're not.) (A "good", "nicely put" and accepted answer?!) – philipxy Jul 18 '15 at 07:46
  • @philipxy this explains why only one primary key, you just need to dive deeper. primary key provides a unique identifier to a table row, which basic mapper into the btree for log(n) access time. I guess that's the reason why most on disk database will genarate a uid for each entity, but we should not confine in one, definitely you can choose two or more columns as primary key when design a new database, but why do that? it takes more disk space and slow down the whole data access time. –  Jan 02 '20 at 04:59
  • @tyan See my answer. And reread my comment. Including "clearly that is not a reason why one must have a primary key" . – philipxy Jan 02 '20 at 06:51
1

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.

philipxy
  • 14,867
  • 6
  • 39
  • 83
  • You are right!, I agree with you at some point now, the more I think about it, the more It's like a bug cause by early designer try to indexing data on the dataset. And this primary key concept should be throw away! –  Apr 22 '20 at 07:56
  • @tyan You might also be interested in [Does an empty SQL table have a superkey? Does every SQL table?](https://stackoverflow.com/a/46029100/3404097) or ["1NF" has no single meaning.](https://stackoverflow.com/a/40640962/3404097) ... or lots of my relational model answers. – philipxy Apr 22 '20 at 08:04
  • Most ppl don't even think about those, they just takes in whatever the textbook mislead them. Sad for them. –  Apr 22 '20 at 08:14
  • @tyan Sadly it seems very human to just accept fuzzy things without noticing they're not clear. One of my favourite DB quotes is ["It all makes sense if you squint a little and don’t think too hard."](http://web.cecs.pdx.edu/~maier/TheoryBook/TRD.html). – philipxy Apr 22 '20 at 08:40
  • 1
    read all the links and it take some time to ingest and I have to admit the theory behind it is rather cogent even I skip some part of long proof, it's really open my eyes and save tons of time. Appreciated it. –  Aug 04 '20 at 04:49
0

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

Raybarg
  • 730
  • 6
  • 12
0

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

Nitin Gupta
  • 27
  • 6
0

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.

NavyPier
  • 45
  • 5
0

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.

Rajesh Kharatmol
  • 141
  • 1
  • 5