Guide to Foreign Key Constraints in MySQL and MariaDB [With Examples]

In this guide, we cover foreign key constraints in MySQL and MariaDB, along with a number of useful examples.

MySQL (and its fork MariaDB) are Relational Database Management Systems (RDBMS) – Database Systems which hold data in tables which can be related to each other.

Tables in an RDBMS are organized into columns that contain data in rows (also called tuples).

Each row will usually have a Primary Key – a unique value to identify the row in the table.

To define relationships between two rows in two different tables, those two rows must have a corresponding value that can be used to match them up. This is the Foreign Key and it is usually set to the value of the Primary Key of the related row in the other table.

With these relationships, useful queries can be written that pull data from a table, as well as related data from other tables – for example, you might query for a list of authors from one table, and include all of the books they have written from a related table.

In SQL, a Foreign Key Constraint is a function of an RDBMS that prevents you from destroying the link between rows in two tables. It means that every value in a Foreign Key column which is used to link a row to another table must contain a value that exists in that other table.

It works in two ways- it prevents you from entering an invalid key into a foreign key column, and it prevents you from deleting a row if a row in another table has a relationship with it.

Foreign Key Example

authors table:

books table:

Here you can see two tables: authors and books – each author has an author_id to uniquely identify them, as each book has a separate book_id. These are the Primary Keys.

Each book also has an author_id to link it to its author – this is the Foreign Key. This column, when set up, can be constrained to only contain existing values from the author_id column in the authors table to ensure that each book has a valid author recorded.

So, for the above example, Stephen King has the books The Running Man and The Shining as those books have an author_id matching the author_id in the authors table.

Creating a Table With A Foreign Key

Foreign key constraints can be added at the creation of a table:

CREATE TABLE books (
    book_id int NOT NULL,
    book_name text NOT NULL,
    author_id int,
    PRIMARY KEY (book_id),
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

The REFERENCES statement is telling your database that author_id in this table (books) should match the one in the authors table.

You can also name your foreign key constraint:

CREATE TABLE books (
    book_id int NOT NULL,
    book_name text NOT NULL,
    author_id int,
    PRIMARY KEY (book_id),
    CONSTRAINT foreign_key_author_book FOREIGN KEY (author_id)
    REFERENCES authors(author_id)
);

The CONSTRAINT statement in conjunction with the REFERENCES statement allows the naming of the constraint – in this case, it is named foreign_key_author_book.

Adding a Foreign Key To An Existing Table

The below assumes you already have an author_id column in your books table, but that you forgot to add a Foreign Key Constraint to it, and wish to do so:

ALTER TABLE books
ADD FOREIGN KEY (author_id) REFERENCES authors(author_id);

And again, this time with a named key:

ALTER TABLE books
ADD CONSTRAINT foreign_key_author_book
FOREIGN KEY (author_id) REFERENCES authors(author_id);

Removing a Foreign Key Constraint from an Existing Table

You can remove (DROP) a foreign key constraint from a table by its name – while keeping the data in the column:

ALTER TABLE books
DROP FOREIGN KEY foreign_key_author_book;

Conclusion

Foreign Key Constraints are an important tool you can use to keep your data valid.

When not used, invalid relationships can be created, or records can be removed, even if other records are still referring to them. This can create messy and confusing data sets.

Using the data above as an example, if foreign key constraints were not used it would be possible to delete an author from the authors table, and you’d never be able to find out who wrote their books.

Check out our other database tutorials!