Editing Primary key

{{Short description|Relational databases concept}}
{{one source|date=June 2021}}
In the [[relational model]] of [[database]]s, a '''primary key''' is a designated attribute ([[Column (database)|column]]) that can reliably identify and distinguish between each individual record in a [[Table (database)|table]]. The database creator can choose an existing unique attribute or combination of attributes from the table (a [[natural key]]) to act as its primary key, or create a new attribute containing a [[unique identifier|unique ID]] that exists solely for this purpose (a [[surrogate key]]). 

Examples of natural keys that could be suitable primary keys include data that is already by definition unique to all items in the table such as a [[national identification number]] attribute for person records, or the combination of a very precise [[timestamp]] attribute with a very precise location attribute for event records.

More formally, a primary key is a <em>specific choice</em> of a <em>minimal</em> set of attributes that uniquely specify a tuple ([[Row (database)|row]]) in a [[Relation (database)|relation]] (table).{{efn|Corresponding terms are respectively theoretical (attribute, tuple, relation) and concrete (column, row, table).}}<ref>{{cite web |url=https://support.office.com/en-us/article/add-or-change-a-table-s-primary-key-in-access-07b4a84b-0063-4d56-8b00-65f2975e4379 |title=Add or change a table's primary key in Access |publisher=Microsoft |access-date=January 20, 2020 |quote=A primary key is a field or set of fields with values that are unique throughout a table.}}</ref> A primary key is a choice{{clarify|date=August 2024}} of a [[candidate key]] (a minimal [[superkey]]); any other candidate key is an '''alternate key'''.

==Design==
In relational database terms, a primary key does not differ in form or function from a key that isn't primary. In practice, various motivations may determine the choice of any one key as primary over another. The designation of a primary key may indicate the "preferred" identifier for data in the table, or that the primary key is to be used for [[foreign key]] references from other tables or it may indicate some other technical rather than semantic feature of the table. Some languages and software have special syntax features that can be used to identify a primary key as such (e.g. the PRIMARY KEY constraint in SQL).

The relational model, as expressed through relational calculus and relational algebra, does not distinguish between primary keys and other kinds of keys. Primary keys were added to the [[SQL]] standard mainly as a convenience to the application programmer.{{citation needed|date=March 2012}}

Primary keys can be an integer that is incremented, a [[universally unique identifier]] (UUID) or can be generated using [[Hi/Lo algorithm]].

==Defining primary keys in SQL==
Primary keys are defined in the [[SQL#Standardization history|ISO SQL Standard]], through the PRIMARY KEY constraint. The syntax to add such a constraint to an existing table is defined in [[SQL:2003]] like this:

<syntaxhighlight lang="sql">
ALTER TABLE <table identifier> 
    ADD [ CONSTRAINT <constraint identifier> ] 
    PRIMARY KEY ( <column name> [ {, <column name> }... ] )
</syntaxhighlight>

The primary key can also be specified directly during table creation. In the SQL Standard, primary keys may consist of one or multiple columns. Each column participating in the primary key is implicitly                                                                          defined as NOT NULL. Note that some [[RDBMS]] require explicitly marking primary key columns as <code>NOT NULL</code>.{{citation needed|date=January 2013}}

<syntaxhighlight lang="sql">
CREATE TABLE table_name (
   
   ...
)
</syntaxhighlight>

If the primary key consists only of a single column, the column can be marked as such using the following syntax:

<syntaxhighlight lang="sql">
CREATE TABLE table_name (
   id_col  INT  PRIMARY KEY,
   col2    CHARACTER VARYING(20),
   ...
)
</syntaxhighlight>

==Surrogate keys==
{{Main article|Surrogate key}}
In some circumstances the natural key that uniquely identifies a tuple in a relation may be cumbersome to use for software development. For example, it may involve multiple columns or large text fields. In such cases, a [[surrogate key]] can be used instead as the primary key. In other situations there may be more than one [[candidate key]] for a relation, and no candidate key is obviously preferred. A surrogate key may be used as the primary key to avoid giving one candidate key artificial primacy over the others.

Since primary keys exist primarily as a convenience to the programmer, surrogate primary keys are often used, in many cases exclusively, in database application design.

Due to the popularity of surrogate primary keys, many developers and in some cases even theoreticians have come to regard surrogate primary keys as an inalienable part of the relational data model. This is largely due to a migration of principles from the object-oriented programming model to the relational model, creating the hybrid object–relational model. In the [[object–relational mapping|ORM]] like [[active record pattern]], these additional restrictions are placed on primary keys:

* Primary keys should be immutable, that is, never changed or re-used; they should be deleted along with the associated record.
* Primary keys should be anonymous integer or numeric identifiers.

However, neither of these restrictions is part of the relational model or any SQL standard. [[Due diligence]] should be applied when deciding on the immutability of primary key values during database and application design. Some database systems even imply that values in primary key columns cannot be changed using the <code>UPDATE</code> SQL statement.{{Citation needed|date=January 2009}}

==Alternate key==
Typically, one candidate key is chosen as the primary key. Other candidate keys become alternate keys, each of which may have a <code>UNIQUE</code> constraint assigned to it in order to prevent duplicates (a duplicate entry is not valid in a unique column).<ref>[http://www.orafaq.com/wiki/Alternate_key Alternate key – Oracle FAQ<!-- Bot generated title -->]</ref>

Alternate keys may be used like the primary key when doing a single-table select or when filtering in a ''where'' clause, but are not typically used to join multiple tables.

==See also==
* [[Natural key]]
* [[Superkey]]
* [[Unique key]]
* [[Candidate key]]
* [[Composite key]]
* [[Relational database]]
* [[Entity–relationship model|Entity relationship diagram]]
* [[Strongly typed identifier]]

==Notes==
{{notelist}}

==References==
{{reflist}}

[[Category:Database management systems]]
[[Category:Data modeling]]