assigning foreign key in mysql

MySQL Tutorial

Mysql database, mysql references, mysql examples, mysql foreign key constraint.

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.

The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.

Look at the following two tables:

Persons Table

Orders table.

Notice that the "PersonID" column in the "Orders" table points to the "PersonID" column in the "Persons" table.

The "PersonID" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.

The "PersonID" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the parent table.

Advertisement

FOREIGN KEY on CREATE TABLE

The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders" table is created:

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

FOREIGN KEY on ALTER TABLE

To create a FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is already created, use the following SQL:

DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL:

COLOR PICKER

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail: [email protected]

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail: [email protected]

Top Tutorials

Top references, top examples, get certified.

Home » MySQL Basics » MySQL Foreign Key

MySQL Foreign Key

Summary : in this tutorial, you will learn about MySQL foreign key and how to create, drop, and disable a foreign key constraint.

Introduction to MySQL foreign key

A foreign key is a column or group of columns in a table that links to a column or group of columns in another table. The foreign key places constraints on data in the related tables, which allows MySQL to maintain referential integrity.

Let’s take a look at the following customers and orders tables from the sample database .

In this diagram, each customer can have zero or many orders and each order belongs to one customer.

The relationship between customers table and orders table is one-to-many . This relationship is established via the foreign key in the orders table specified by the customerNumber column.

The customerNumber column in the orders table links to the customerNumber primary key column in the customers table.

The customers table is called the  parent table or referenced table , and the orders table is known as the child table or referencing table .

Typically, the foreign key columns of the child table often refer to the primary key columns of the parent table.

A table can have more than one foreign key where each foreign key references a primary key of the different parent tables.

Once a foreign key constraint is in place, the foreign key columns from the child table must have the corresponding row in the parent key columns of the parent table, or values in these foreign key columns must be NULL (see the SET NULL action example below).

For example, each row in the orders table has a customerNumber that exists in the customerNumber column of the customers table. Multiple rows in the orders table can have the same customerNumber .

Self-referencing foreign key

Sometimes, the child and parent tables may refer to the same table. In this case, the foreign key references back to the primary key within the same table.

See the following employees table from the sample database .

The reportTo column is a foreign key that refers to the employeeNumber column which is the primary key of the employees table.

This relationship allows the employees table to store the reporting structure between employees and managers. Each employee reports to zero or one employee and an employee can have zero or many subordinates.

The foreign key on the column reportTo is known as a recursive or self-referencing foreign key.

MySQL FOREIGN KEY syntax

Here is the basic syntax of defining a foreign key constraint in the CREATE TABLE or ALTER TABLE statement:

In this syntax:

First, specify the name of the foreign key constraint that you want to create after the CONSTRAINT keyword. If you omit the constraint name, MySQL automatically generates a name for the foreign key constraint.

Second, specify a list of comma-separated foreign key columns after the FOREIGN KEY keywords. The foreign key name is also optional and is generated automatically if you skip it.

Third, specify the parent table followed by a list of comma-separated columns to which the foreign key columns reference.

Finally, specify how the foreign key maintains the referential integrity between the child and parent tables by using the ON DELETE and ON UPDATE clauses. The reference_option determines the action that MySQL will take when values in the parent key columns are deleted ( ON DELETE ) or updated ( ON UPDATE ).

MySQL has five reference options: CASCADE , SET NULL , NO ACTION , RESTRICT , and SET DEFAULT .

• CASCADE : if a row from the parent table is deleted or updated, the values of the matching rows in the child table are automatically deleted or updated.
• SET NULL :  if a row from the parent table is deleted or updated, the values of the foreign key column (or columns) in the child table are set to NULL .
• RESTRICT :  if a row from the parent table has a matching row in the child table, MySQL rejects deleting or updating rows in the parent table.
• NO ACTION : is the same as RESTRICT .
• SET DEFAULT : is recognized by the MySQL parser. However, this action is rejected by both InnoDB and NDB tables.

MySQL fully supports three actions: RESTRICT , CASCADE and SET NULL .

If you don’t specify the ON DELETE and ON UPDATE clause, the default action is RESTRICT .

MySQL FOREIGN KEY examples

Let’s create a new database called fkdemo for the demonstration.

1) RESTRICT & NO ACTION actions

Inside the fkdemo database, create two tables categories and products :

The categoryId in the products table is the foreign key column that refers to the categoryId column in the  categories table.

Because we don’t specify any ON UPDATE and ON DELETE clauses, the default action is RESTRICT for both update and delete operations.

The following steps illustrate the RESTRICT action.

1) Insert two rows into the categories table:

2) Select data from the categories table:

3) Insert a new row into the products table:

It works because the categoryId 1 exists in the categories table.

4) Attempt to insert a new row into the products table with a categoryId   value does not exist in the categories table:

MySQL issued the following error:

5) Update the value in the categoryId column in the categories table to 100 :

MySQL issued this error:

Because of the RESTRICT option, you cannot delete or update categoryId 1 since it is referenced by the productId 1 in the products table.

2) CASCADE action

These steps illustrate how ON UPDATE CASCADE and ON DELETE CASCADE actions work.

1) Drop the products table:

2) Create the products table with the ON UPDATE CASCADE and ON DELETE CASCADE options for the foreign key:

3) Insert four rows into the products table:

4) Select data from the products table:

5) Update categoryId 1 to 100 in the categories table:

6) Verify the update:

7) Get data from the products table:

As you can see, two rows with value 1 in the categoryId column of the products table was automatically updated to 100 because of the ON UPDATE CASCADE action.

8) Delete categoryId 2 from the categories table:

9) Verify the deletion:

10) Check the products table:

All products with categoryId 2 from the products table was automatically deleted because of the ON DELETE CASCADE action.

3) SET NULL action

These steps illustrate how the ON UPDATE SET NULL and ON DELETE SET NULL actions work.

1) Drop both categories and products tables:

2) Create the categories and products tables:

The foreign key in the products table changed to ON UPDATE SET NULL and ON DELETE SET NULL options.

3) Insert rows into the categories table:

4) Insert rows into the products table:

5) Update categoryId from 1 to 100 in the categories table:

7) Select data from the products table:

The rows with the categoryId 1 in the products table was automatically set to NULL due to the ON UPDATE SET NULL action.

8) Delete the categoryId 2 from the categories table:

9) Check the products table:

The values in the categoryId column of the rows with categoryId 2 in the products table was automatically set to NULL due to the ON DELETE SET NULL action.

Drop MySQL foreign key constraints

To drop a foreign key constraint, you use the ALTER TABLE statement:

• First, specify the name of the table from which you want to drop the foreign key after the ALTER TABLE keywords.
• Second, specify  the constraint name after the DROP FOREIGN KEY keywords.

Notice that constraint_name is the name of the foreign key constraint specified when you created or added the foreign key constraint to the table.

To obtain the generated constraint name of a table, you use the SHOW CREATE TABLE statement:

For example, to see the foreign keys of the products table, you use the following statement:

The following is the output of the statement:

As you can see clearly from the output, the table products table has one foreign key constraint: fk_category

This statement drops the foreign key constraint of the products table:

To ensure that the foreign key constraint has been dropped, you can view the structure of the products table:

Disabling foreign key checks

Sometimes, it is very useful to disable foreign key checks e.g., when you import data from a CSV file into a table .

If you don’t disable foreign key checks, you have to load data into a proper order i.e., you have to load data into parent tables first and then child tables, which can be tedious.

However, if you disable the foreign key checks, you can load data into tables in any order.

To disable foreign key checks, you use the following statement:

And you can enable it by using the following statement:

In this tutorial, you have learned about the MySQL foreign key and how to create a foreign key constraint with various reference options.

MySQL Foreign Keys Tutorial and Examples

In this article, we introduced what foreign keys are, the rules for foreign keys, and how to use them in MySQL.

In relational databases, foreign keys are used to define a constrained relationship between two entities. Foreign keys are useful for ensuring data integrity.

What is foreign keys?

Foreign keys are used to refer to other tables relative to the primary key. The foreign key associates the rows of the child table with the rows of the parent table through one or more columns of the child table corresponding to the primary key or unique key value of the parent table.

Let’s take a look at the tables country and city from Sakila sample database . Here is their relationship diagram:

Here is some data from the country table:

Here is some data from the city table:

From this we can see that the country table and the city table is a one-to-many relationship. A country can have multiple cities, and a city can only be located in one country.

If a country already has a city, then you cannot easily delete the country from the country table, otherwise it will cause the city data to be incomplete. You also can’t set a non-existent for a city country_id , otherwise the city data will be wrong.

Foreign key constraints ensure that the data is complete and correct.

Foreign Key Syntax

Let’s look at the foreign key constraints defined by the city table:

Note some of it:

A foreign key is defined here:

After the CONSTRAINT keyword fk_city_country is the name of the foreign key. It is optional.

Following the FOREIGN KEY keyword is the column name that is the foreign key.

Following the REFERENCES keyword are the referenced tables and columns.

ON DELETE and ON UPDATE specify the constraint strategy to be taken when deleting or updating data in the referenced table. You can use one of the following 3 strategies:

• CASCADE : If a row in the referenced table is deleted or updated, the value of the matching row in child table is automatically deleted or updated.
• SET NULL : If a row in the referenced table is deleted or updated, the value of the matching row in child table is set to NULL .
• RESTRICT : A MySQL error is raised when attempting to delete or update a row in the referenced table if a row in the referenced table has a matching row in child table. This is the default policy.

Usually, the table to which the foreign key belongs is called the child table, and the table referenced by the foreign key is called the parent table.

Add Foreign Key Syntax

If the foreign key is not defined when the table is created, you can also add the foreign key later by using the following syntax:

• Use the ALTER TABLE statement to modify the definition of the table.
• Use to ADD [CONSTRAINT foreign_key_name] clause add a constraint named foreign_key_name . [CONSTRAINT foreign_key_name] is optional.
• A foreign key is defined using FOREIGN KEY (column)) REFERENCES parent_table_name (column) .

Delete Foreign Key Syntax

To drop a foreign key on a table, one of the following two syntaxes can be used:

• ALTER TABLE table_name DROP FOREIGN KEY foreign_key_name ;
• ALTER TABLE table_name DROP CONSTRAINT constraint_name ;
• Specify the foreign key name after DROP FOREIGN KEY , that is, the constraint name.
• Specify the constraint name after DROP CONSTRAINT . It can remove any constraint by name, not just foreign keys.

FOREIGN KEY Examples

The following example will create user and user_hobby two tables in the testdb database. Where foreign keys are used in the user_hobby table to reference the user table. Let’s create the user table first, and the user_hobby table will be created according to the respective situation in the following examples. Please follow the steps below:

Log in to the MySQL database as the root user:

Enter the password of the root user.

Note: You can also log in as any other user with appropriate database privileges.

Select the testdb database using the following statement:

If you haven’t created the database yet, run the following statement first:

Create a table named user :

Insert two rows in to the user table:

So far, we have created the user table.

CASCADE strategy

If ON DELETE and ON UPDATE use the CASCADE strategy:

• When a row in the parent table is deleted, the matching row in the child table is also deleted.
• When the key value of the row in the parent table is updated, the fields of the matching row in the child table are also updated.

Use the following SQL to create the user_hobby table with a foreign key using the CASCADE strategy.

Insert rows into two tables:

At this point the rows in the user_hobby table as following:

Let’s take a look at the associated operation of the child table caused by the UPDATE and DELETE operation on the parent table:

UPDATE operate on the parent table

We modify the value of the key user_id in the parent table user from 1 to 100 :

At this point the rows in the user_hobby table:

We found that the column values ​​of those rows in the table user_hobby that match user_id = 1 in user table are also modified to 100 .

DELETE operate on the parent table

At this point the data in the user_hobby table:

We found that those rows in the user_hobby table that matched user_id = 100 in user table were deleted.

RESTRICT strategy

If the ON DELETE and ON UPDATE use the RESTRICT strategy:

• MySQL prohibits deletion of rows in parent tables that match child tables.
• MySQL prohibits deleting the value of the key of the row in the parent table that matches the child table.

Use the following SQL to create the user_hobby table with foreign keys using the RESTRICT strategy.

Insert data into two tables:

Let’s see the result of the UPDATE and DELETE operation on the parent table:

The MySQL server returned the following error:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`testdb`.`user_hobby`, CONSTRAINT `fk_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT)

SET NULL policy

If the ON DELETE and ON UPDATE use the SET NULL strategy:

• When a row of the parent table is deleted, the value of the column of the matching row in the child table is set to NULL .
• When the key value of a row in the parent table is updated, the column value of the matching row in the child table is set to NULL .

Use the following SQL to create the user_hobby table with foreign keys using the SET NULL strategy.

After updated the parent table rows, these rows related in the child table are set to NULL .

Since the above example modifies the data of the table, we reinitialize the data of the two tables:

After deleted the parent table rows, these rows related in the child table are set to NULL .

Self-referencing foreign keys

Sometimes, the child table and the parent table may be the same table. A foreign key in such a table is called a self-referential foreign key.

Typically, self-referential foreign keys are defined in tables that represent tree-like data structures. For example, a table representing categories:

In this table, the parent_category_id column is a foreign key. It references the category_id column of the category table.

This table implements an infinite hierarchy of classification trees. A category can have multiple subcategories, and a subcategory can have 0 or 1 parent category;

Enable or Disable Foreign Key Constraints

To disable foreign key constraints, use the following statement:

To enable foreign key constraints, use the following statement:

Disabling foreign key constraints is useful when bulk importing data.

In this article, we introduced what foreign keys are, the rules for foreign keys, and how to use them in MySQL. Here are the main points of this article:

• Foreign keys are used to define constraints between two entities. Foreign keys are useful for ensuring data integrity.
• The table that defines the foreign key is called the child table, and the table referenced by the foreign key is called the parent table.
• The foreign key refers to the primary key or unique key column of the parent table.
• The ALTER TABLE ... ADD FOREIGN KEY ... statement can be used to add foreign keys.
• The ALTER TABLE ... DROP FOREIGN KEY ... statement can be used to delete foreign keys.
• A self-referential foreign key refers to the table itself. This is used to implements tree-like data structures.

On this page

Working with FOREIGN KEY in MySQL 8: A Developer’s Guide

Introduction.

When dealing with relational databases, understanding the intricacies of foreign keys is essential for maintaining data integrity and establishing relational links between tables. In this developer’s guide, we will delve into the use of FOREIGN KEY constraints in MySQL 8, covering everything from the basics to more advanced topics with practical examples along the way.

Understanding FOREIGN KEY Constraints

A FOREIGN KEY is a field (or collection of fields) in one table that uniquely identifies a row of another table. It is fundamentally a field that creates a link between two tables. The table containing the foreign key is called the ‘child’ table, and the table containing the candidate key is referred to as the ‘referenced’ or ‘parent’ table.

In MySQL, FOREIGN KEY constraints are used to enforce referential integrity rules to ensure that relationships between records in different tables remain consistent.

Creating FOREIGN KEY Constraints

To define a FOREIGN KEY constraint during the creation of a table, use the FOREIGN KEY and REFERENCES keywords:

This code snippet creates an ‘orders’ table with a FOREIGN KEY that points to the ‘customer_id’ in the ‘customers’ table.

Adding FOREIGN KEY Constraints to Existing Tables

If the table already exists, you can add a FOREIGN KEY constraint using the ALTER TABLE statement:

This SQL command modifies the ‘orders’ table by adding a new FOREIGN KEY constraint on the ‘customer_id’ column.

ON DELETE and ON UPDATE Actions

MySQL allows you to define how foreign key relationships react to deletion or updating of related rows through ON DELETE and ON UPDATE clauses:

In this example, if an order is deleted, all its related order details are also deleted (CASCADE), while updates to the ‘order_id’ will not trigger any action on the ‘order_details’ rows (NO ACTION).

Checking FOREIGN KEY Constraints Compatibility

When creating FOREIGN KEY constraints, MySQL requires that the data types and lengths of the parent and child columns match. These constraints need to be checked for compatibility:

Parent column data type: INT(11) Child column data type: INT. These match, and therefore, the FOREIGN KEY constraint can be applied.

Resolving FOREIGN KEY Constraint Errors

At times, FOREIGN KEY issues may arise such as when the child table contains values that don’t exist in the parent table. To identify these, you can use diagnostic queries:

This query will identify orphan records in the child table that don’t have corresponding entries in the parent table.

Removing FOREIGN KEY Constraints

To drop a FOREIGN KEY constraint, you’ll need to know the name of the constraint. It can be done using the ALTER TABLE statement followed by DROP FOREIGN KEY:

This will remove the FOREIGN KEY constraint named ‘customer_ibfk_1’ from the ‘orders’ table.

Composite Foreign Keys

n MySQL, a composite foreign key is a foreign key that consists of two or more columns. This is often used when the foreign key references a primary or unique key consisting of multiple columns. Here’s an example to illustrate how to create and use composite foreign keys in MySQL.

MySQL Example: Creating and Using Composite Foreign Keys

Step 1: create parent table with composite primary key.

First, we’ll create a parent table ( department ) with a composite primary key consisting of two columns.

Step 2: Create Child Table with Composite Foreign Key

Next, we create a child table ( employee ) that references the composite primary key in the department table.

In this example:

• Has a composite primary key consisting of dept_id and dept_name .
• Includes columns dept_id and dept_name , which together form a composite foreign key.
• The FOREIGN KEY constraint is defined to reference the composite primary key in the department table.

Step 3: Inserting Data

Before inserting data into the employee table, make sure to insert the corresponding data into the department table.

Explantion:

• The employee table’s foreign key ( dept_id , dept_name ) references the composite primary key ( dept_id , dept_name ) of the department table.
• When inserting data into employee , the values for dept_id and dept_name must exist in the department table, ensuring referential integrity.
• This setup illustrates a typical scenario where an employee is associated with a department, and the department’s identification involves both an ID and a name.

Composite foreign keys are essential for maintaining data integrity in databases with complex relationships, especially when a single-column foreign key is insufficient to uniquely identify related rows in another table.

The FOREIGN KEY constraint is a powerful tool for database integrity and relationships management. With this guide, you should understand the basics of defining, adding, and troubleshooting foreign keys in MySQL 8, ensuring that your applications handle data responsibly and efficiently.

Next Article: How to install/update MySQL on Windows

Previous Article: One-to-Many Relationship in MySQL 8: A Practical Guide

Series: MySQL Tutorials: From Basic to Advanced

Related Articles

• How to implement cursor-based pagination in MySQL (3 examples)
• MySQL: How to reset the AUTO_INCREMENT value of a table
• MySQL: How to add a calculated column to SELECT query
• MySQL: Eliminate orphan rows in one-to-many relationships
• MySQL: Using R-Tree Indexes for Spatial Data Types
• How to Create Custom Collations in MySQL
• Using Hash Indexes in MySQL: A Practical Guide
• Understanding Full-Text Indexes in MySQL
• Partial Indexes in MySQL: A Practical Guide
• MySQL: How to Remove FOREIGN KEY Constraints
• Using ENUM in MySQL 8: A Practical Guide (with Examples)
• MySQL: Creating a Fixed-Size Table by Using Triggers

Search tutorials, examples, and resources

• PHP programming
• Symfony & Doctrine
• Laravel & Eloquent
• Tailwind CSS
• Sequelize.js
• Mongoose.js

Referential Constraints and Foreign Keys in MySQL

• foreign key

Foreign keys and referential constraints allow you to set relationships between tables and modify some of the database engine’s actions. This beginner’s guide explains referential integrity and foreign key use in MySQL.

One of the most important aspects of database usage is being able to trust the information you store. Database engines provide several features that help you maintain the quality of your data, like defining required columns as NOT NULL and setting an exact data type for each column.

There are situations where information in one table has a relationship with information in another table, like having a bookseller’s warehouses in one table and storing books associated with each warehouse in a separate table. Often, this relationship needs to be strengthened by including references to the other table. To do this, we define a set of “rules” known as foreign keys and referential constraints that tell the database how to reinforce these relationships.

Let’s start with the simple scenario of a bookseller. This bookseller needs to store records in several interrelated database tables. The model is shown below.

The Scenario

The bookseller uses a simple database that tracks books of various genres . These books are stored in several warehouses . This is what the data model looks like:

There is a table for each entity, but we are not yet able to associate a given book to its genre or indeed to the warehouse where it is stored. To do this, we need two new columns in the “ books ” table:

• A column to store the relevant warehouse
• A column to store the book’s genre

One possibility could be to simply add the warehouse name and genre to the “ books ” table, like this:

Let’s populate the “ warehouses ” table with some sample data:

And now we’ll insert a record for a book in the “Depo3” warehouse:

You may have already noticed how this would be prone to inconsistencies. For instance, you could save a record with a “ warehouse_name ” that doesn’t exist in the “ warehouses ” table:

Or you might update a warehouse (or genre) and forget to update the associated books:

In the record below, we can see there is still a book pointing to “Depo3” (which doesn’t exist as far as the database knows):

There is another problem. Suppose we want to add a book to the “Depo1” warehouse:

There are two warehouses called “Depo1”. If we went by just this facility name, the database wouldn’t know which warehouse the book belongs to.

You may think that this confusion could never happen to you. Why would you add a book to a warehouse that doesn’t exist or forget to update a field? This actually happens all the time; people manually enter a wrong value or there’s a fault in the software that connects to your database. Trust me, it will happen.

The fix is to model your database in a way that maintains the integrity of your data. SQL provides foreign keys (a.k.a. FKs) to help us with this task. Let’s see how it works.

What Are Foreign Keys?

Let me begin by showing you how the above tables will look with foreign keys:

Foreign keys are about reinforcing relationships at a database level . The line between the tables in the above diagram gives you an idea of how the tables are related. Also the FK label next to “ warehouse_id ” and “ genre_id ” clearly identifies what columns are “linking” these tables.

We’ll now back up a bit and assume we’re working with the unconnected tables shown in the previous section. The first thing to do is to insert some sample data for warehouses and genres:

And now we’ll insert a book pointing to a warehouse with id=1 and a genre with id=2 :

This record will insert flawlessly since the data is “consistent”. In other words, we are inserting a book associated with a warehouse and a genre that exist in the database. But what if the book referred to values that didn’t exist? Try this one:

This INSERT is associating a book with a warehouse that doesn’t exist in our database. Therefore, the command fails. The relationship defined in the foreign key is not met and we are prevented from storing inconsistent data.

Now that we have the general picture, let’s examine foreign keys in more detail and see how they work in a variety of scenarios.

Using Foreign Keys

A foreign key is a column that uniquely references a record in another table. In our example, each table has an “ id ” column that uniquely identifies each row. We use the “ warehouse_id ” and “ genre_id ” to “connect” these tables to the “ books ” table. Note that primary keys are most likely to be used as foreign keys, but other UNIQUE columns can also be used.

Adding a Foreign Key to a Table

To add foreign keys, the first step is to create the columns in the “ books ” table that will store the references to the other tables. We’re following a naming convention, so we’ll name the columns using the table name (singular), an underscore ( _ ), and the referenced column (in this case, “ id ”). As previously mentioned, this gives us “ warehouse_id ” and “ genre_id ”.

Our model now looks like this:

We are ready to add the foreign keys. Let’s start with the warehouse relationship:

As you can see the query is self-explanatory: we modify (alter) the “ books ” table to add a constraint identified as fk_books_warehouses_warehouse_id . This instructs the engine to set the “ warehouse_id ” column as a reference to the “ id ” column in the “ warehouses ” table.

The constraint name is optional, but if you supply one it must be unique to the database. A naming convention you could use is fk_[referencing table name]_[referenced table name]_[referencing field name] .

Next, we do the same for the books-genres relation:

As anticipated, our model now looks like this:

Foreign Keys and Relational Integrity

How do foreign keys protect the relationships between tables and records? To answer this question, let’s use some examples. (Note: We will use the terms “parent” and “child” to describe records and tables. Put simply, a “child” record is a record that contains foreign keys. In this case, the book record is the child record because it references both the warehouse and genre tables (the parent tables). As we will see, the child record is dependent on the data in the parent tables.)

We need to populate the “ warehouses ” and “ genres ” with fresh data. First, we clean the tables of any previous records:

And now we can insert new records:

By default, the constraint we added will ensure the following:

Because it points to a warehouse that doesn’t exist, it fails:

• If you delete a warehouse or genre record (a parent record) that has a book (child) record related to it, the deletion will not be performed: DELETE FROM warehouses WHERE id = 1; #the warehouse where Alice in wonderland book is Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails
• The same is true for updates; any update of a parent record will not be performed if there are existing child records. UPDATE warehouses SET id=22 WHERE id = 1; #the warehouse where Alice in wonderland book is Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails

However, if we try to update another warehouse that has no books, this command works:

What are Referential Constraints?

We know we can’t delete or update a warehouse or genre if there is any book record pointing to it. Let’s imagine we have thousands of books in a warehouse. What if we want to delete that warehouse from our database and keep the consistency of our data? We would first delete all the books and then delete the warehouse. This will eliminate the foreign key restriction error.

This behavior can be configured by defining referential constraints. When we define the “ books ” table’s foreign keys, we specify how we want the engine to “react” when an update or a delete occurs on related parent records.

The syntax is generally as follows:

It’s important to note that it is not mandatory to set both referential actions ( ON DELETE , ON UPDATE ). Also, we do not need to set these actions to the same outcome. Let me show you some possible situations based on our bookseller scenario:

ON DELETE CASCADE and ON UPDATE CASCADE in MySQL

When a delete occurs on the row from the parent table, setting CASCADE will automatically delete the matching rows in the child table .

In a real-world scenario, we could have a software requirement that whenever a warehouse deposit gets deleted from our system then all its associated books are also deleted. We could simply write a referential constraint, like this:

When we set ON DELETE CASCADE , we are instructing the engine to propagate the deletion performed on the warehouse to its related books. We can now try deleting the warehouse where Alice in Wonderland is located and see if it works:

The deletion has gone through!

Likewise, say that warehouse id numbers change over time and all books pointing to the warehouse must also be updated to match. This complex operation can be solved by setting a referential constraint for the update action:

The ON UPDATE CASCADE tells the database that when an update occurs on the referenced column from the parent table (“ id ”), it must automatically update the matching rows in the child table (“ books ”) with the new value.

Let’s insert a new book into Warehouse 3:

If we update the id of Warehouse 3, the database will also update the book record:

The “ warehouse_id ” for this book is now 33, as we expected:

These constraints can be combined; we can have both actions configured for the DELETE and UPDATE actions, as shown below:

Note: You can drop the previous constraint by running:

Now you can run:

ON DELETE SET NULL and ON UPDATE SET NULL in MySQL

Like CASCADE , we can use SET NULL on delete and update operations. The foreign key column from the child table (books) will be set to NULL when the parent record gets updated or deleted.

Suppose we decide that deleting a warehouse should delete all its books, but deleting a genre should not delete the books linked to that genre (since the books still exist in the warehouse). So when a genre is deleted, we simply remove the reference to the deleted genre. We do this by saving a NULL value in the “ genre_id ” column for the related books.

To achieve this, we employ SET NULL as a constraint when creating the foreign key:

Important! Before you specify the SET NULL action, make sure that you have declared the “ genre_id ” column in the “ books ” table as NULL able.

SET NULL can also be defined for updates, like this:

This is a relatively rare situation; it’s more common to have ON UPDATE set to CASCADE . As I explained earlier, though, we don’t need to set the same action for both UPDATE and DELETE ; for instance, I can SET NULL when a deletion occurs but use a CASCADE for an update, like this:

RESTRICT and NO ACTION

Setting RESTRICT is the same as omitting the ON DELETE or ON UPDATE clause; it rejects the delete or update operation for the parent table if the table has associated children. In other words, if you do not specify ON DELETE or ON UPDATE , by default you invoke RESTRICT .

In our scenario, we might only allow the deletion of unused genres (i.e. genres that have no books associated with them). If there is a book pointing to the “ genre_id ” we want to delete, the operation should be RESTRICT ed (rejected).

This referential constraint will do the trick:

NO ACTION is an SQL keyword that, in MySQL, is equivalent to RESTRICT . (Note: Other databases may interpret this command differently.) NO ACTION rejects the delete or update operation for the parent table if there is a related foreign key value in the child table.

Want to Learn More?

I’ve introduced some initial concepts about the complex world of data integrity and consistency. There is a lot involved in mastering this, mostly in terms of database and table design. For this reason, I encourage you to take a look at LearnSQL.com’s  Creating Tables in SQL  course. It is designed to be easy to understand, yet also comprehensive enough to show you how to create optimal tables in SQL.

Never worked with SQL and don’t know how to use it? That’s OK! Just make sure to check the SQL Basics course first.

You may also like

How Do You Write a SELECT Statement in SQL?

What Is a Foreign Key in SQL?

Enumerate and Explain All the Basic Elements of an SQL Query

• SQL Cheat Sheet
• SQL Interview Questions
• MySQL Interview Questions
• PL/SQL Interview Questions
• Learn SQL and Database

How to add a foreign key using ALTER in MySQL

• SQL Query to Add Foreign Key Constraints Using ALTER Command
• SQL Query to Drop Foreign Key Constraint Using ALTER Command
• SQL Query to Add Unique key Constraints Using ALTER Command
• MySQL | Deleting rows when there is a foreign key
• SQL Query to Drop Unique Key Constraints Using ALTER Command
• Foreign Key Constraint in SQL
• SQL ALTER TABLE - ADD, DROP, MODIFY
• SQL Query to Add a New Column After an Existing Column in SQL
• ALTER (RENAME) in SQL
• How to Add an Identity to an Existing Column in MySQL?
• What Can be a Foreign Key in Another Table?
• MySQL FOREIGN KEY Constraint
• How to Add a Column to a MySQL Table in Python?
• How to Rename a Column in MySQL?
• How to Reset Auto Increment in MySQL
• Foreign Key in MariaDB
• How to Add an Identity to an Existing Column in PostgreSQL?
• Inserting data into a new column of an already existing table in MySQL using Python
• PostgreSQL - ALTER TRIGGER
• SQL | DDL, DQL, DML, DCL and TCL Commands
• SQL | Join (Inner, Left, Right and Full Joins)
• SQL | WITH clause
• How to find Nth highest salary from a table?
• SQL Trigger | Student Database
• SQL | GROUP BY

In this article, we will discuss the overview of foreign keys and will discuss how to add a foreign key using ALTER in MySQL step by step. Let’s discuss it one by one.

Foreign key :  If an attribute is a primary key in one table but was not used as a primary key in another table then the attribute which is not a primary key in the other table is called a foreign key. If the changes made or any data is manipulated in any of the tables the changes get reflected in both the tables with the help of foreign key constraint.

Steps to add a foreign key using ALTER in MySQL : Here let us see how to add an attribute of student which is the primary key in the student table as a foreign key in another table exam as follows.

Step-1: Creating a database university : Here, you will see how to create a database in MySQL as follows.

Step-2: Using the database university : Here, you will see how you can use the existing database which you have already created as follows.

Step-3: Creating a table student : Here, you will see how to create a table in MySQL as follows.

Step-4: Viewing the description of the table : Here, you will see how to verify the table as follows.

Output : Here, as you can see in the description the key column of student_id is PRI which means it is the primary key in that table student.

Step-5: Creating another table exam : In this step, you will see one more table for reference.

Step-6: Viewing the description of the table : In this step, you can verify the table you have created.

Step-7: Adding another column student_id into the exam table : Here, you will see how to add another column student_id into the exam table as follows.

Step-8: Making a foreign key : Here, you will see how to make the student_id attribute foreign key in the exam table which is the primary key in the student table as follows.

Syntax –

Query –

Step-9: Verifying the exam table :  Here, you will see the description of the exam table as follows. 

Output : Now as you can see in the description of the table exam one more column student_id is added and in the Key column of description, the student_id has MUL which means it is a foreign key.

Please Login to comment...

Similar reads.

Improve your Coding Skills with Practice

What kind of Experience do you want to share?

How To Implement MySQL Foreign Key Constraints In Your Database Design

This article delves into MySQL foreign keys, focusing on their practical application in database management. Tailored for developers, it covers creating, modifying, and effectively utilizing foreign keys to maintain data integrity and streamline database relationships.

💡 KEY INSIGHTS

• Understanding foreign keys in MySQL is crucial for maintaining data integrity and enforcing relationships between tables.
• Foreign keys establish links between tables, preventing actions that would violate referential integrity.
• By defining foreign keys , you ensure that only valid data can be inserted into related tables, reducing errors and improving data consistency.
• Properly configuring and managing foreign keys is a fundamental skill for database designers and administrators.

Foreign keys in MySQL are essential for maintaining data integrity and establishing relationships between tables in a database. As a programmer or developer, understanding how to effectively use these constraints can significantly enhance your database management skills. This article explores the practical aspects of implementing and utilizing MySQL foreign keys in various scenarios, providing you with the knowledge to apply these concepts in your projects.

Understanding MySQL Foreign Keys

Creating foreign keys in mysql, modifying and deleting foreign keys, handling foreign key constraints and errors, common use cases and examples, performance considerations with foreign keys, frequently asked questions, syntax of creating a foreign key, updating and deleting foreign keys, importance of indexing foreign keys.

Foreign keys in MySQL are a fundamental aspect of relational database design. They are used to create a link between two tables, ensuring that the data in one table corresponds accurately to data in another. This relationship not only maintains data integrity but also facilitates complex queries and data analysis.

A foreign key is a column or a set of columns in one table that references the primary key columns of another table. The primary purpose is to enforce referential integrity between the data in the two tables.

When creating a foreign key in MySQL, the syntax typically looks like this:

Example Of Foreign Key Creation

Consider two tables: employees and departments . Each employee is assigned to one department, and this relationship is maintained through a foreign key.

To modify or delete a foreign key, you use the ALTER TABLE command. For instance, to delete a foreign key:

And to add or modify a foreign key:

Indexing foreign keys is crucial for performance. When a foreign key is indexed, MySQL can quickly navigate between the child and parent tables, significantly speeding up query execution.

In summary, understanding and effectively implementing MySQL foreign keys is essential for robust database design and management. This knowledge ensures data consistency and optimizes database performance.

Syntax For Creating Foreign Keys

Altering an existing table to add a foreign key, ensuring referential integrity.

Creating a foreign key in MySQL involves defining a relationship between two tables. This is typically done during the table creation or by altering an existing table.

When creating a new table, you can define a foreign key using the FOREIGN KEY keyword followed by the foreign key constraints.

Example: Creating A Table With A Foreign Key

Let's create a orders table that references a customers table:

If you need to add a foreign key to an existing table, use the ALTER TABLE statement:

Example: Adding A Foreign Key To An Existing Table

Suppose we have an existing products table and want to link it to the orders table:

When creating foreign keys, MySQL ensures referential integrity by only allowing values in the foreign key column that exist in the referenced primary key column. This prevents orphan records and maintains consistent data across tables.

Deleting A Foreign Key

Modifying a foreign key, caution when modifying foreign keys.

In MySQL, altering or removing a foreign key is a common task during database maintenance and restructuring. It's important to handle these operations with care to maintain data integrity .

To delete a foreign key, you need to know the foreign key's name. This can be found using the SHOW CREATE TABLE command. Once you have the name, use the ALTER TABLE statement.

Example: Deleting A Foreign Key

Suppose you have a foreign key named fk_customer_id in the orders table and you want to remove it:

Modifying a foreign key typically involves two steps: dropping the existing foreign key and then creating a new one with the desired properties.

Example: Modifying A Foreign Key

If you need to change the foreign key in the orders table to reference a different column in the customers table:

When modifying foreign keys, it's crucial to ensure that the changes align with your data model and do not lead to orphaned records or broken relationships. Always verify the impact of these changes on your database's integrity and functionality.

Understanding Constraint Violations

Handling deletion constraints, using on delete and on update, error diagnosis and resolution.

Working with foreign keys in MySQL often involves dealing with constraints and handling errors that arise from these constraints. Understanding how to manage these situations is crucial for maintaining a robust database.

A common issue when working with foreign keys is a constraint violation . This occurs when an operation violates the rules set by the foreign key, such as trying to insert a record with a non-existent foreign key value.

Example: Inserting With Invalid Foreign Key

Consider a table orders with a foreign key referencing customers . If you try to insert an order with a customer ID that doesn't exist in customers , MySQL will throw an error.

When you try to delete a record from a parent table that is referenced by a foreign key in a child table, MySQL will prevent this action to avoid orphaned records.

Example: Deleting With Referenced Foreign Key

Attempting to delete a customer that has orders linked to it will result in an error:

To handle these constraints more gracefully, you can define actions like ON DELETE and ON UPDATE in your foreign key constraints.

Example: Cascading Delete And Update

With ON DELETE CASCADE , deleting a record in customers will automatically delete the related records in orders .

When encountering foreign key errors, carefully check the operations leading to the error. Ensure that the foreign key values exist in the parent table and that there are no orphaned records in the child table. Properly structuring your foreign key constraints and understanding their implications are key to smooth database operations.

Enforcing Data Integrity

Cascading actions, simplifying data retrieval, preventing invalid data entry.

Foreign keys in MySQL are used in various scenarios to maintain data integrity and define relationships between tables. Understanding these use cases through examples can help in effectively applying these concepts.

Foreign keys are primarily used to enforce data integrity . They ensure that a value in one table corresponds to a value in another table.

Example: Employee and Department Relationship

Consider two tables: employees and departments . Each employee belongs to a department.

Foreign keys can be used to define actions that automatically happen in a related table, like cascading updates or deletes .

Example: Automatically Deleting Related Records

If a department is deleted, you might want to delete all employees in that department.

Foreign keys simplify the process of joining tables and retrieving related data.

Example: Fetching Data Across Tables

To get the names of employees along with their department names:

Foreign keys prevent the entry of invalid data in the foreign key column, ensuring referential integrity .

Example: Preventing Invalid Department IDs

Trying to insert an employee with a non-existent department ID will result in an error:

Impact On Insert Update And Delete Operations

Indexing foreign key columns, considerations for large datasets, disabling foreign keys for bulk operations, regular maintenance.

When implementing foreign keys in MySQL, it's important to consider their impact on database performance . While foreign keys enforce data integrity, they can also affect the speed of data operations.

Foreign keys can slow down INSERT , UPDATE , and DELETE operations. This is because MySQL must check referential integrity in real-time during these operations.

Example: Insert Operation With Foreign Key Check

Proper indexing of foreign key columns is crucial. Without indexes, MySQL must perform a full table scan on the referenced table, significantly impacting performance.

Example: Creating Index on Foreign Key Column

In databases with large datasets , the overhead of maintaining foreign key constraints can be more pronounced. It's important to balance the need for data integrity with performance requirements.

For bulk data operations, temporarily disabling foreign key checks can improve performance. However, this should be done cautiously to avoid data integrity issues.

Example: Disabling Foreign Key Checks

Regular database maintenance , such as optimizing tables and updating statistics, can help in maintaining the performance of databases with foreign keys.

How do foreign keys affect database performance?

Foreign keys can impact performance, especially during insert, update, and delete operations, as MySQL needs to check referential integrity. Proper indexing of foreign key columns can mitigate some of these performance issues.

Can foreign key constraints be temporarily disabled?

Yes, in MySQL, you can temporarily disable foreign key checks, particularly useful during bulk data operations. This can be done using SET foreign_key_checks = 0; However, it's important to re-enable checks and ensure data integrity afterward.

Are foreign keys automatically indexed in MySQL?

In MySQL, foreign keys in InnoDB tables are automatically indexed. However, it's good practice to explicitly define indexes, especially if you're working with other storage engines or need to ensure optimal performance.

Can a foreign key reference a non-primary key column?

Yes, a foreign key can reference a unique key in another table, not just primary keys. However, the referenced column must be a unique key to maintain proper referential integrity.

Let’s test your knowledge!

What does a foreign key in MySQL represent?

Continue learning with these mysql guides.

• How To Use MySQL Create User For Effective Database Management
• Compare MariaDB Vs MySQL: Key Differences And Considerations
• What Is MySQL: Exploring The Essentials Of Database Management
• How To Effectively Utilize MySQL Join In Database Queries
• How To Use MySQL List Databases Command Effectively

Subscribe to our newsletter

Subscribe to be notified of new content on marketsplash..

7.6 Using Foreign Keys

MySQL supports foreign keys, which permit cross-referencing related data across tables, and foreign key constraints, which help keep the related data consistent.

A foreign key relationship involves a parent table that holds the initial column values, and a child table with column values that reference the parent column values. A foreign key constraint is defined on the child table.

This following example relates parent and child tables through a single-column foreign key and shows how a foreign key constraint enforces referential integrity.

Create the parent and child tables using the following SQL statements:

Insert a row into the parent table, like this:

Verify that the data was inserted. You can do this simply by selecting all rows from parent , as shown here:

Insert a row into the child table using the following SQL statement:

The insert operation is successful because parent_id 1 is present in the parent table.

Insertion of a row into the child table with a parent_id value that is not present in the parent table is rejected with an error, as you can see here:

The operation fails because the specified parent_id value does not exist in the parent table.

Trying to delete the previously inserted row from the parent table also fails, as shown here:

This operation fails because the record in the child table contains the referenced id ( parent_id ) value.

When an operation affects a key value in the parent table that has matching rows in the child table, the result depends on the referential action specified by ON UPDATE and ON DELETE subclauses of the FOREIGN KEY clause. Omitting ON DELETE and ON UPDATE clauses (as in the current child table definition) is the same as specifying the RESTRICT option, which rejects operations that affect a key value in the parent table that has matching rows in the parent table.

To demonstrate ON DELETE and ON UPDATE referential actions, drop the child table and recreate it to include ON UPDATE and ON DELETE subclauses with the CASCADE option. The CASCADE option automatically deletes or updates matching rows in the child table when deleting or updating rows in the parent table.

Insert some rows into the child table using the statement shown here:

Verify that the data was inserted, like this:

Update the ID in the parent table, changing it from 1 to 2, using the SQL statement shown here:

Verify that the update was successful by selecting all rows from the parent table, as shown here:

Verify that the ON UPDATE CASCADE referential action updated the child table, like this:

To demonstrate the ON DELETE CASCADE referential action, delete records from the parent table where parent_id = 2 ; this deletes all records in the parent table.

Because all records in the child table are associated with parent_id = 2 , the ON DELETE CASCADE referential action removes all records from the child table, as shown here:

For more information about foreign key constraints, see FOREIGN KEY Constraints .