🚀 Think you’ve got what it takes for a career in Data? Find out in just one minute!

Unlocking the Power of SQL Foreign Keys: Definition and Applications

-
3
 m de lecture
-
Discover what SQL foreign keys are and how they enhance database integrity and relationships, enabling efficient data management and ensuring referential integrity in relational database systems.

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:

SQL DELETE: How do I use this query?
SQL Developer: What is it? What’s it for?
SQL Tutorial: Top 5 Most Useful Methods
SQL joins: everything you need to know about table associations
SQL vs NoSQL: differences, uses, advantages and disadvantages
Facebook
Twitter
LinkedIn

DataScientest News

Sign up for our Newsletter to receive our guides, tutorials, events, and the latest news directly in your inbox.

You are not available?

Leave us your e-mail, so that we can send you your new articles when they are published!
icon newsletter

DataNews

Get monthly insider insights from experts directly in your mailbox