4

In database code, I see references to a PRIMARY KEY. What is a PRIMARY KEY? and what difference does it make if some of the columns in one of my tables form a PRIMARY KEY... or don't form a PRIMARY KEY.

MendelG
  • 14,885
  • 4
  • 25
  • 52
Raedwald
  • 46,613
  • 43
  • 151
  • 237
  • 2
    This question has been asked on SO before. A quick search reveals http://stackoverflow.com/questions/9551195/mysql-what-is-a-primary-key, but I question whether this is a legitimate question for SO at all. The answer is easily Googled and is fundamental to any study of databases. I might also direct you to your own answers: http://meta.stackoverflow.com/questions/270522/helping-users-who-are-unable-unwilling-to-google/270568#270568 and http://meta.stackoverflow.com/questions/267516/when-is-a-question-worth-asking/267519#267519 – pjd Apr 19 '15 at 04:04
  • 3
    @pjd Whether Google gives an answer is not relevant. Wherher a question is fundamental is not relevant. – Raedwald Apr 19 '15 at 08:04
  • I disagree. An answer that is easily Googled (and/or found on this site) implies a lack of research effort, which is a reason for downvoting a question (offered in the hover text for the downvote button). As you yourself said, "A question is worth asking if, and only if, it is on topic, clear, specific and includes the fruits of adequate relevant research." – pjd Dec 31 '15 at 17:51
  • Readers should beware that in SQL PK means something--a distinguished non-empty UNIQUE set of NOT NULL columns--different than in the relational model--a distinguished possibly emtpy unique set of (non-null, if you think they're allowed) columns not containing a smaller one. – philipxy Jun 07 '19 at 04:11
  • 1
    Does this answer your question? [MYSQL - What is a primary key?](https://stackoverflow.com/questions/9551195/mysql-what-is-a-primary-key) – philipxy Mar 04 '21 at 22:56

4 Answers4

2

1st Normal Form requires that you have a unique key for you to have a relation. If this requirement is not met (i.e. if you have no unique keys), then your table will be just a heap, not a relation.

The primary key is more or less (i.e. roughly speaking) one specially selected unique key. The one who designs the schema chooses it. The main difference between a PK and a unique key is that unique keys can contain NULL values while PKs cannot. Also, you can have more than one unique keys in a given table but at most one PK.

By making one of the unique keys a primary key, you allow other tables to easily point to this table via their foreign keys (FKs). Technically the FKs (of the child tables) can also point to any unique key (from the parent table) but usually people use for that purpose the primary key (PK) which (as said) is basically just one of the unique keys. That means FKs usually point to PKs.

For more details, see also:

What is the difference b/w Primary Key and Unique Key

Community
  • 1
  • 1
peter.petrov
  • 38,363
  • 16
  • 94
  • 159
1

A primary key is a key in a relational database that is unique for each record.

If a reference to a column in a database is primary key, then that value will be unique. when you try to add another row to the value with the same primary key, it throws an error.

If you dont have a primary key on a column, you can add any non-unique values as well.

alekya reddy
  • 934
  • 1
  • 8
  • 14
1

Structurally speaking, a primary key is a column/group of columns that has a unique index and can't be null. And each table can have at most one primary key (although a primary key can have more than one field).

Semantically speaking, a PK uniquely identifies a row in a table. Due to its constrains, you can be 100% sure that there are no two records having the same PK value. This allows optimisations on both the SQL server and the SQL client side

Cristik
  • 30,989
  • 25
  • 91
  • 127
1

Primary key (abbreviated PK) is an index of a table with not null constraint. The main conclusion of having a not null index on a table is to have a unique identifier of each table row. Primary key can be built on a single column or multiple different columns of the same table as well as a regular index. Developers usually choose a unique identifier (e.g. ‘id’) to be a primary key, even though there is no strict definition what must be the primary key.

In relational databases primary keys are used belong concept of atomicity in 1st normal form (1NF) to refer a table row from any other table, that means you have the same data/row just once in the database and use its primary key to make a reference.

The last important think about primary keys is better performance. Indexes generally help with performance issues in meny cases. However in the case of primary key, you can expect that you often access table rows using the primary key as the access condition or where clause (e.g. SELECT * FROM mytable WHERE id = 3). You can expect that your DBMS organizes data on a storage to be fast accessible using the primary key. Without having a primary key on the table you can expect some performence issues.

Further reading on Primary Keys:

Martin Strejc
  • 4,307
  • 2
  • 23
  • 38