Relational databases contain thousands and thousands of rows. To simplify processing, it is vital to identify the different records in a table clearly and efficiently. This is where primary keys come in.
Primary keys, unique fields in your data table
The primary key refers to one (or more) attribute(s) which allows records to be fully differentiated. In practical terms, it is a single field for all the records in a table.
The advantages of a primary key
The purpose of primary keys is to structure the database so that it is easier to identify the rows in the table. In doing so, primary keys make it possible to:
- Guarantee data integrity and accuracy: each row is only recorded once and no important information is lost.
- Simplify the update process: simply select the different primary keys to modify or delete rows.
- Quicker access to data: if you know the table’s primary key, you can access the data you need more easily.
Example of a primary key
To help you better understand primary keys, here is an example:
| Name | First Name | Age |
|---|---|---|
| Dupont | Jeanne | 25 |
| Leroy | Marie | 21 |
| Dupont | Pierre | 21 |
In this example, only the “First name” attribute is used to differentiate the rows in the table. And for good reason, in the name, “Smith” is found 2 times. The same applies to age 21. The first name attribute is therefore a primary key.
However, this is no longer the case with the following table:
| Name | First Name | Age |
|---|---|---|
| Dupont | Jeanne | 25 |
| Leroy | Jeanne | 21 |
| Dupont | Pierre | 21 |
In this case, no single attribute can be used to differentiate between records. So you need to select several attributes, such as surname + first name. By combining the two, you can see that there is never the same line twice. Here, the last name + first name attributes constitute a primary key.
Good to know: databases often contain an ID attribute which serves as the primary key. This involves assigning a distinct number to each row (1,2,3,4…). This primary key is created automatically at the very beginning of the table (column A) in order to differentiate each record.
This is why the term primary key is very often confused with identifier. Although the two are often identical, this is not automatic.
The different types of primary key
There are different types of primary keys. The most common are :
- Natural keys: these are attributes that contain significant information (without which it is impossible to make a mistake). This is the case, for example, with a social security number.
- Substitution keys: these are artificial keys, such as an automatically generated number.
- Composite keys: this is a combination of several attributes, such as the first and last names of customers.
Primary and foreign keys
The ultimate purpose of the primary key is to link related records in other tables. For example, the customer ID can be used in a table grouping all the company’s customers and another table grouping customers by salesperson.
This is where foreign keys come into play. These are one or more attributes which are the primary key in an A table and which are used to characterise the elements in a B table. In this case, it is generally the ID (of a customer, a product, an employee, etc.) that is used in several different tables.
To find out more about data management, sign up for one of our training courses.
Key facts:
- Primary keys are unique fields used to differentiate rows in a database.
- Most of the time, primary keys are IDs (or identifiers). But this is not always the case.
- Foreign keys are used to link data between different tables.
