Can Multiple Primary Keys Exist on a Single Table?
- What are Keys?
- Can a Table Contain Multiple Primary Keys?
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
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 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
Concatenated or Compound Keys
Taken one step further from
simple keys are
compound keys. As the name implies, a
concatenated key is a joining of multiple
single keys. For example, the system may automatically combine the
single keys into a
concatenated key, like so:
primary 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
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
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
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 and
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