In SQL, why do we need a primary key if we can use NOT NULL and UNIQUE constraints in place of a primary key?

Question

A primary key in SQL is a combination of both NOT NULL and UNIQUE constraints, with the only difference that only one primary key constraint can exist within a table.

Why can't we only manage with the NOT NULL and UNIQUE constraints? Even they both do the same thing.

Possible duplicate of [difference between primary key and unique key](https://stackoverflow.com/questions/9565996/difference-between-primary-key-and-unique-key) — philipxy, Jun 01 '19 at 12:07
@philipxy you posted the link a number of minutes after the first, and after I'd loaded the page, but before I'd completed my comment - so I didn't see it initially. I also posted a second comment pointing out that the link you give doesn't have an accepted answer that deals with unique non-null columns, but that appears to have disappeared. — Steve, Jun 01 '19 at 13:43
@Steve I mentioned comment timing just wanted to tell you that I left a link unprompted. I was not saying that you should have known. I flagged your comments to me as "no longer needed" because they were specific to me & our exchange is subsumed (perhaps equivocally) by [help] pages. Seems all got flagged or moderated away with them. My last comment addressed both of yours, I said link answers were irrelevant. (So is acceptance.) PS I--presumably like you--am trying to best balance a lot of conflicting goals utlimately to make a repository of useful & findable Qs & As. — philipxy, Jun 01 '19 at 20:35
@philipxy, agreed. I will only say that, in a sufficiently large repository that has accreted unsystematically over time such as this (and which often contains dross, too), it will often be necessary for a user to ask a question, even if only to be answered by someone who actually knows where to find the answer. It seemed unfair on the OP in all the circumstances for him to get downvotes (not necessarily any from you) and a link that didn't give as good an answer as that which Gordon had already given at the time you commented. — Steve, Jun 01 '19 at 21:32

score 5 · Accepted Answer · edited Jun 01 '19 at 11:34

The definition of a primary key is:

A primary key is unique.
A primary key is not null.
Table has only one primary key.

You are asking about the third condition. Well, that is the definition. The "primary key" is a single set of keys that have been explicitly chosen to uniquely identify each row in the table. The word "primary" implies that there is only one per table. Other keys or combinations of keys that meet the first two conditions are called candidate primary keys.

Although not strictly enforced, primary keys are the best method for referencing individual rows. They should be used for foreign key constraints, for instance (and any database that I come into contact with does enforce primary keys for foreign key constraints). Having multiple different keys refer to a single table confuses the data model. Think about Entity-Relationship modeling. The links should be primary keys.

To give a flavor of the use of primary keys, some databases (such as MySQL using the InnoDB storage engine) by default cluster tables based on the primary key. A table can only be clustered once, hence the use of a single key.

In other words, if a table (a) contains a unique-and-not-null key which is the only such key, then that is the primary key. If a table (b) contains no such key, then it has no primary key. If a table (c) contains multiple such keys, then the designation of which is primary (if any) is purely conventional in the application, with the proviso still that not more than one such key can be designated primary. Keys meeting the criteria of being primary but not designated as such, are called "candidate primary keys". A primary key may or not be clustered, and clustering may or not be on a primary key — Steve, Jun 01 '19 at 11:50

score 2 · Answer 2 · edited Jun 27 '19 at 08:56

You pointed a differences between a primary key and unique constraints, but this is not the only differences between them.

The key differences between a primary key and a unique key is - a clustered index is automatically created when a primary key is defined whereas a unique key generates the non-clustered index. You can do some Google search to get an idea on a clustered and non-clustered index.

Another differences is - a primary key is a unique field on a table, but it is special in the sense that the table considers that row as its key. This means that other tables can use this field to create foreign key relationships to themselves. A unique constraint simply means that a particular field must be unique.

In SQL, why do we need a primary key if we can use NOT NULL and UNIQUE constraints in place of a primary key?

2 Answers2