If you're designing an SQL database management system, it's in your interest to know and implement the foreign key system. In the long term, it's a win-win situation.
A foreign key is a column (or several columns) in a database that is linked to the primary key column of another table. The primary key in question is a simple identifier. The useful information – which is subject to change – is contained in the external table.
What use is a foreign key?
Let’s imagine you’re managing an Articles table, in which there’s a column called Category. For each item, this category must be filled in individually.
For example, we might have a category called “Metal product”. However, from one day to the next, legislation changes and this category becomes “Metal-based product”.
What do we do now? Will we have to modify all the lines in which the “Metal product” category appears? Unfortunately, yes. If the Articles table covers tens of thousands of entries, the procedure will be tedious, to say the least.
It’s to avoid this kind of situation that the notion of foreign key has been defined. So, in the case mentioned above, the idea is to create a separate table, called Category, in which the primary key is an identifier (a number for each category) associated with the corresponding description.
From there, in the Articles table, all we need to do is indicate, for each product offered in the catalog, the corresponding identifier from the Categories table.
It will then be possible to print the list of each item while displaying (thanks to a join) the corresponding category.
So, as we can see, the situation of the category that changes its name is solved here.
Parent and daughter table
A “parent table” is one that is referenced by another table. This would be the case here of the Categories table.
And we speak of a “daughter table” for one that refers to an external table – which would be the case here of the Articles table.
A practical example
Here’s another example of the use of a foreign key in a more explicit case.
CLIENTS table
| Id_Client | Name |
First Name |
Age |
|---|---|---|---|
| 1 | Martin |
Germaine |
32 |
| 2 |
Culprit |
Thomas |
22 |
| 3 |
Albanez |
Francis |
40 |
| 4 |
De Jonquière |
Martine |
33 |
CLIENTS is the “relative” table. If customer n°4, Martine de Jonquière, were to divorce and resume her maiden name (Heras) tomorrow, we would simply modify this information in the “relative” table.
Table COMMANDES
| Id_Commande |
Date_Commande |
Id_Client |
|---|---|---|
| 1 |
11/04/2023 |
4 |
| 2 |
10/05/2023 |
3 |
| 3 |
12/05/2023 |
4 |
| 4 |
26/05/2023 |
4 |
| 5 |
16/06/2023 |
1 |
In this example, we find the “Customer_ID” column in the ORDERS table (which is the “daughter” table). It points to the “Id_Client” column in the CLIENTS table. In this way, we would know by means of a join that Martine de Jonquières placed three orders: once in April and twice in June.
In the CUSTOMERS table, the Customer_ID column is the primary key.
In the ORDERS table, the Customer_ID column is the foreign key.
Advantages of foreign keys
Referential integrity
The main advantage of using foreign keys is referential integrity: no row in the child table will be complete and valid until a corresponding record exists in the parent table.
Consistency of information
All tables referencing the foreign key are guaranteed to produce consistent, up-to-date data. As a reminder, if Martine de Jonquières changes her name, this change will be visible in all tables referencing her identifier.
Disadvantages of foreign keys
Reduced processing speed
Of course, the foreign key will mean longer processing times. Each time you insert a new row in a table such as ORDERS, you’ll need to check whether the corresponding customer identifier exists in the CUSTOMERS table.
Complexity
Referring to customer_id n°4 may appear more complex than simply indicating the name Martine de Jonquières. And so, a priori, reading a database such as the ORDERS database will not be very explicit at first.
Recommended use
Despite the disadvantages of using foreign keys, the use of foreign keys is highly recommended for anyone responsible for designing SQL database management systems. In the long term, it’s a win-win situation. To put it simply, it’s important to use foreign keys whenever you’re dealing with a type of data that is likely to evolve over time. The examples given here clearly illustrate what can be gained. And so, it’s crucial to define foreign keys right from the design stage of an SQL database.
💡Related articles:
