How to Add, Show, and Drop MySQL Foreign Keys

A key is typically defined as a column or a group of columns that are used to uniquely locate table records in relational databases (including MySQL, of course). Keys help retrieve data from those tables in the most precise and effective way.

And now that we’ve covered MySQL primary keys on our blog, it’s time to give you a similarly handy guide on foreign keys—namely, give you a brief overview of what they are and tell you how to create, manage, and delete them in MySQL.

Contents

• What is a foreign key in MySQL?
• The syntax for MySQL foreign keys
• How to create foreign keys in MySQL
• How to show foreign keys
• How to drop foreign keys
• How to disable foreign key checks in MySQL

What is a foreign key in MySQL?

As usual, we’ll start with the definition. As we mentioned, a foreign key is a column or a group of columns that allow cross-referencing related data across tables in a database. A foreign key relationship comprises a parent table (that contains the initial column values) and a child table (with column values that reference those of the parent table). A foreign key constraint is defined on the child table.

Why use MySQL foreign keys?

Let’s have a brief look at the primary reasons for using foreign keys.

Reason #1. The cornerstone of it all—which will be stated by nearly every article on foreign keys that you will find—is that foreign keys will help you constantly maintain the referential integrity of your databases. Moreover, you won’t need to overcomplicate things and store the same data in different tables simultaneously. We’ll elaborate more on this with an example in the corresponding section.

Reason #2. With foreign keys defining relationships between tables, it is easier to get a clear picture of database design, which in turn helps you troubleshoot possible issues.

Reason #3. Finally, foreign keys help achieve better performance of your queries. It does not happen directly, since the actual performance boost is gained through the use of indexes. However, foreign keys are indexed automatically because they are often used for searching, which makes them a vital part of search optimization.

The syntax for MySQL foreign keys

Now that we know the definition and the main reasons to use foreign keys, let’s take a look at the general syntax used for defining a foreign key constraint in a CREATE TABLE or ALTER TABLE statement.

[CONSTRAINT [constraint_name]] FOREIGN KEY
    [index_name] (column_name, ...)
    REFERENCES table_name (column_name,...)
    [ON DELETE referential_action]
    [ON UPDATE referential_action]

referential_action:
    RESTRICT | CASCADE | SET NULL | NO ACTION

You can specify the name for your foreign key constraint in CONSTRAINT [constraint_name]. It is optional, though; in case you don’t indicate it, MySQL will generate the name for your constraint automatically.

In ON UPDATE and ON DELETE subclauses, you can specify referential actions that will define the result of an UPDATE or DELETE operation affecting a key value in the parent table that has matching rows in the child table. Referential actions include the following:

• CASCADE deletes or updates the row from the parent table and automatically deletes or updates the matching rows in the child table.
• SET NULL deletes or updates the row from the parent table and sets the foreign key column/columns in the child table to NULL.
• RESTRICT rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause.
• InnoDB treats NO ACTION as RESTRICT; the delete or update operation for the parent table is rejected if there’s a related foreign key value in the referenced table. Meanwhile, NDB supports deferred checks, and NO ACTION specifies a deferred check; when it is used, constraint checks are not performed until commit time.
• The official MySQL documentation for FOREIGN KEY constraints also mentions the fifth action, SET DEFAULT; however, both InnoDB and NDB currently reject table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.

Now, if that looks too general, let’s take a closer look at a crystal clear basic example.

How to create foreign keys in MySQL

Now let’s proceed to the actual how-to part of this article, where we’ll show you the examples of creating, showing, and dropping foreign keys. To make it even more convenient, we’ll use dbForge Studio for MySQL, our high-end IDE that addresses nearly every aspect of the development, management, and administration of MySQL and MariaDB databases—including the handling of foreign keys.

How to create a new table with foreign keys

To make it clear from the start, let’s illustrate the syntax with an easy example.

For instance, imagine that we have an existing table called customer with a primary key сustomer_id. Then, we need to create a second table called orders, and we want to make sure that each order in it is linked to a valid customer. So, when we write a CREATE TABLE statement for orders, we add a foreign key referencing the сustomer_id column of the customer table. This is what it looks like.

CREATE TABLE orders (
    order_id int NOT NULL,
    order_number int NOT NULL,
    customer_id smallint UNSIGNED NOT NULL,
    PRIMARY KEY (order_id) );

To create the orders table, we simply run the following query in the Studio’s SQL editor.

How to add foreign keys to an existing table

In case we already have an existing orders table, things are even easier. We simply use ADD CONSTRAINT…FOREIGN KEY in the ALTER TABLE statement.

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer_id
FOREIGN KEY (customer_id)
REFERENCES customer(customer_id);

This is what the eventual query looks like in the Studio.

Whichever way we choose, creating or altering, the result is the same – we have two tables linked with a foreign key.

And like we previously promised to explain, this is where the foreign key helps maintain the referential integrity of your databases. Let’s imagine that your customer table has records of 10 customers with IDs spanning 1 to 10. Now, if you attempt to enter a record in the orders table with a сustomer_id that does not exist in the customer table (for instance, 11), the database will reject your entry and show a warning message. Therefore, you won’t break the said integrity by assigning an order to a customer that does not exist. And, as we said, you don’t have to store the same data in different tables simultaneously; the сustomer_id column in the customer table is just enough, and you simply refer to it with a foreign key.

How to show foreign keys

You might need to view the foreign key constraints defined in a database, for instance, when performing debugging or modeling the database structure. Here is the general syntax for it.

SELECT
    table_name,
    column_name,
    constraint_name,
    referenced_table_name,
    referenced_column_name
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
	referenced_table_schema = 'database_name'
    AND referenced_table_name = 'table_name';

This is what it looks like for our case.

SELECT
    table_name,
    column_name,
    constraint_name,
    referenced_table_name,
    referenced_column_name
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
	referenced_table_schema = 'sakila'
    AND referenced_table_name = 'customer';

Note that you can also view data from related tables using the Foreign Key Lookup feature of the Studio. To do that, you need to retrieve the data of the required table, and then simply view the data of any related table directly in Data Editor. To see how it’s done, refer to Foreign Key Lookup in our documentation.

How to drop foreign keys

Finally, there are situations when you might need to remove foreign keys; for instance, due to changes in database design or in case you need to fix incorrectly set constraints. To drop a foreign key, you need to know the name of the foreign key constraint. Once you have it, you can use the DROP FOREIGN KEY command inside an ALTER TABLE statement to drop it. For instance, this is what it looks like for our case, where we want to drop the foreign key from the orders table.

ALTER TABLE orders
 DROP FOREIGN KEY fk_orders_customer_id;

How to disable foreign key checks in MySQL

You may want to temporarily disable foreign key checks. This can be crucial for tasks like batch loading of data, where enforcing constraints with each insert would be plain inefficient. To disable foreign key checks, you need to set the FOREIGN_KEY_CHECKS system variable to zero, as follows.

SET FOREIGN_KEY_CHECKS = 0;

Make sure you use this option with caution as it can lead to data integrity issues. For instance, MySQL will not validate any data that has been added after disabling the checks.

Finally, after you perform the required operations, you can reset the said variable to 1, so that it re-enforces foreign key constraints.

SET FOREIGN_KEY_CHECKS = 1;

Alternatively, you can use the View Server Variables window of dbForge Studio for MySQL to manage variables visually. To access it, go to the Database menu > Server Variables.

Bonus: Video guide to MySQL foreign keys

If you would like to get even more information and examples of foreign keys managed in dbForge Studio for MySQL, take a look at our comprehensive video guide.

Download dbForge Studio for a free 30-day trial today!

Besides database design and handling of foreign keys, there are a lot of features that you can explore in dbForge Studio for MySQL. You can do it for free – just download the Studio for a 30-day trial and use this time to get firsthand experience with an IDE that will definitely make your daily work with MySQL and MariaDB a breeze.