Foreign keys in MySQL/MariaDB

Foreign keys are an important part of relational databases. In this article we will explore what they do, when to use them and how to use them.

For this article, we will use two simples tables as a demonstration: users and user_logins. The users table to contain a simple list of users, and the user_logins table to contain a log of all login attempts (successes and failures).

Basic Table Structure

Data Integrity

Foreign keys are used to create a link between two tables. They are used when data in one table relies on data in another. You can use foreign keys to create a constraint on data that prevents the data that is relied upon being deleted or modified in such a way that breaks the relationship.

Here's an example. You have a user in the users table. They have 5 login attempts stored in the user_logins table. If you delete the user suddenly you have 5 login attempts that no longer have a corresponding user. Using foreign keys, you can specify a couple of ways to handle this:

  • ON DELETE CASCADE - When you delete a record in users, all the corresponding user_logins are also deleting
  • ON UPDATE CASCADE - When you update a record in users, all the corresponding user_logins are updated. (Only the foreign key will be updated, such as the user_id if changed)
  • ON DELETE RESTRICT - Prevents deleting a record in users if there are any corresponding records in user_logins
  • ON UPDATE RESTRICT - Prevents updating a record in users if there are any corresponding records in user_logins and this update would break the relationship (an update to the ID)

There are also SET NULL, SET DEFAULT and NO ACTION which we will explore in a future article.

Adding the foreign keys

We can add a foreign key are table creation or alter the table to add the foreign key.

alter table user_logins
    add constraint my_key_name
        foreign key (user_id) references users (user_id)
            on update cascade on delete cascade;

This query alters the user_logins to add the foreign key (called my_key_name). It cascades any update and delete.

Structure with Foreign Keys

As we can see in the diagram, we now have a foreign key between the two tables.

When they are needed

You should, in a perfect world, use a foreign key whenever there is a relationship between two tables. It is very common for tables to reference other tables, so you it's common to have many foreign keys set up within a database.

Although foreign keys in practice can cause some issues. Foreign keys slow down writes to the database as every time you write you are checking and updating the foreign key itself.

They can also cause some more risky scenarios to appear. Cascading updates/deletes can mean that large amounts of data can be delete because of a single query. Restricting updates/deletes can cause queries to fail and this needs to be handled correctly.

It's important to think carefully about how you use foreign keys to prevent such issues.

Written by Kieran on 2019-08-11
This website uses Cookies to enhance your experience. Close Manage