Can multiple primary keys exist on a single table?
Posted by: AJ Welch
While many developers and database administrators may work with primary keys everyday, it is a fascinating topic to ask oneself, “What exactly is a primary key and can (or should) a database table contain multiple primary keys simultaneously?”
Below we’ll examine these questions in more detail and try to come to the reasonable and generally agreed upon consensus within the development community.
What are keys?
To understand what a primary key is in a database table, we must first understand a little bit about non-primary keys. A key in a table is simply an attribute that is used to identify and access that information. A table can and often will have multiple keys, such as in the table Users both email and username could be considered keys.
Depending on the developer or administrator you’re speaking to, you may hear about a variety of key-types and their definitions, so we’ll just cover a few different examples below and a basic definition of each.
Simple keys
A simple key is just a key using only one single attribute in the table. Unless we impose more restrictions on the key or the table, then the username attribute in the above example is a simple key.
Concatenated or compound keys
Taken one step further from simple keys are concatenated or compound keys. As the name implies, a concatenated key is a joining of multiple single keys. For example, the system may automatically combine the last_name and year_of_birth single keys into a concatenated key, like so: smith1980.
Primary keys
A [primary key](https://en.wikipedia.org/wiki/Unique_key) is a key which has been chosen to be the principal (or primary) representative attribute for that row of data. The primary key is unique and that attribute is then used throughout the database and is accessed and passed around to other tables as the representative attribute for the data in question.
In practice, the primary key attribute is also marked as NOT NULL in most databases, meaning that attribute must always contain a value for the record to be inserted into the table.
As an example, either the email or username simple keys could be assigned the designation of the primary key, but typically it is best practice to set the primary key to an attribute that isn’t (or couldn’t) be changed by either the business logic or even by the individual. For example, imagine a User gets a new email address, which then causes all past primary key associations made using the old email address to become invalid when using the new email address.
For this reason (among others), most primary keys use a number or unique string, such as a [UUID](https://en.wikipedia.org/wiki/Universally_unique_identifier).
Numeration and auto-incrementing
It’s also briefly worth noting that many database systems are setup in such a way that every table has a primary key that is both numeric and is also auto-incremented. This simply means that the database engine itself automatically assigns each new record in that table a unique primary key value that is incrementally larger than all previous values. However, most developers agree that this practice is out of date and exposes unnecessary security flaws for the system when used for some tables that represent certain data.
For example, imagine all User records are assigned an auto-incremented primary key value, know as the id attribute. If a malicious person discovers that the id attribute of a given user (e.g. John Smith) is the value 1500, this exposes a bit of information already. First, it indicates that there are likely a minimum of at least 1499 other users in the system, or were at some point. It also means that if John Smith’s user page can be accessed via a URL or API call which contains that id value of 1500, then there’s a good chance simply changing the value to another number, such as 1499 or 1501, will expose the page of another user that may not want their page accessed by this visitor. In this manner, records can be queried by simply guessing the id values on a mass scale.
These are obviously very simple examples, but for these reasons most modern databases will use a randomized and unique primary key attribute value such as a UUID when working with sensitive data.
Can a table contain multiple primary keys
The short answer is no, a table is not allowed to contain multiple primary keys, as that goes against the fundamental principles of relational database design (see: [database normalisation](https://en.wikipedia.org/wiki/Database_normalisation) and [Third normal form](https://en.wikipedia.org/wiki/Third_normal_form)).
It is possible for a table to have multiple candidate keys, which effectively behave similar to a primary key in that a candidate key is unique, NOT NULL, and is a singular representation of that table record.
However, when it comes to selecting which one attribute will be assigned as the primary key for the table, the choice comes from the list of all potential candidate keys (hence the name, they are candidates for becoming a primary key). Ultimately, only one candidate key is selected as the best representative attribute for that record, to be used in this table as the primary key and referenced elsewhere in the database by other tables via their respective foreign keys.