A primary key is one or more columns that have been configured as the unique identifier field for the table.
Most primary keys are comprised of a single column, but they can also be comprised of multiple columns.
Any value stored in a primary key field is unique to that record. No other record contains that value. The value is a unique identifier.
Examples of Primary Key Values
Unique identifiers are often an incrementing number (eg, 1, 2, 3…) but this is not a requirement. A unique identifier could be any string – as long as its unique. For example, a primary key field could be:
- an automatically generated number
- social security number
- an email address (but only if two users can’t share the same email address)
- vehicle identification number
- driver licence number
- some other special code that is unique to each record
Are Primary Keys Necessary?
With a few (arguable) exceptions, all records in a relational database should have a primary key.
Without primary key fields, querying relational databases would be riddled with problems. In fact, primary keys are an essential part of relational database management systems. They enable you to query related data across multiple tables while having full confidence that all data returned is correct.
For example, finding all products that customer number “101” has purchased is easy, as long as the customer number is unique, and the field has been defined as the primary key. If it wasn’t unique, it would be possible that more than one customer has a number of “101”, in which case, you wouldn’t know which products were purchased by which customer.
And if it wasn’t defined as the primary key, it couldn’t be used in the primary key/foreign key part of the relationship. Relationships work by having a foreign key linked to the primary key of another table. Because the foreign key value in table B is the same as the primary key value in table A, we know that the whole record in table B is related to the whole record in table A.
Therefore, primary keys are an essential part of relationships in relational database management systems.
About Candidate Keys
Any column that can guarantee uniqueness is called a candidate key.
But just because it’s a candidate key it doesn’t necessarily follow that it’s a primary key. Any candidate key would need to be specified as the primary key first.
So it is possible that more than one column in a table can guarantee uniqueness, but only one of them is defined as the primary key.
About Composite Keys
A composite key is a candidate key defined by more than one column.
Therefore, a primary key can consist of more than one column. Two or more columns can be specified as being the primary key. In this case, the value of these columns is combined to create a unique identifier. Each column on its own doesn’t necessarily guarantee uniqueness but when the columns are combined, they must guarantee uniqueness if they are to be used as a primary key.