Do you really need Foreign Keys?

Relational database - Edition #21

Hey, I'm Marco and welcome to my newsletter!

As a software engineer, I created this newsletter to share my first-hand knowledge of the development world. Each topic we will explore will provide valuable insights, with the goal of inspiring and helping all of you on your journey.

In this episode, I introduce the concept of foreign keys in relational databases such as MySQL by showing the advantages and disadvantages of using them

---

👋 Introduction

Imagine we want to represent a reality where we have customers and the orders they have placed, with a one-to-many relationship.

In UML we could represent it like this:

customers → orders relation

that in MySQL is reflected in this DDL:

CREATE TABLE customers
(
    id    INT AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(50),
    email VARCHAR(100)
);

CREATE TABLE orders
(
    id          INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date  DATE,
    amount      DECIMAL(10, 2)
);

To establish a 1-to-many relationship, we create the foreign key customer_id in the orders table. This allows each order to be associated with the respective customer who placed it. In this way, we created a connection from the orders table to the customers table.

Foreign keys

The purpose of foreign keys is to facilitate navigation and linking between two tables, managed through a CONSTRAINT in the database. This means that when inserting new records in tables containing foreign keys, the database ensures that the value entered for the FK exists in the reference table, thus guaranteeing so-called referential integrity.

The foreign key constraint is specified during the definition of the database tables. In the previous example of customers and orders, we can make the constraint explicit like this:

CREATE TABLE customers
(
    id    INT AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(50),
    email VARCHAR(100)
);

CREATE TABLE orders
(
    id          INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date  DATE,
    amount      DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers (id)
);

this constraint introduces advantages and disadvantages in its use, which will be addressed in the following sections.

---

Pros

✅ Database consistency