Foreign Key Constraint in MySQL

Back to: MySQL Tutorials for Beginners and Professionals

Foreign Key Constraint in MySQL with Examples

In this article, I am going to discuss the Foreign Key Constraint in MySQL with Examples. Please read our previous article where we discussed the Primary Key Constraint in MySQL with examples before proceeding to this article.

What is a Foreign Key Constraint in MySQL?

Creating the relationship between the database tables is one of the most important concepts in a database. The relationship between multiple tables provides a mechanism for linking the data stores in multiple tables and retrieving them in an efficient manner.

In order to create a link between two tables, we must specify a Foreign Key in one table that references a column in another table. That means the Foreign Key constraint in MySQL is used for binding two tables with each other and then verify the existence of one table data in other tables.

Note: A foreign key in one TABLE points to either a primary key or a unique key in another table in MySQL. The foreign key constraints are basically used to enforce referential integrity. In our upcoming articles, we will discuss referential integrity constraints in detail.

How to Create Foreign Key Constraint in MySQL?

To Create a Foreign Key Constraint in MySQL, we require the following things

1. We require two tables for linking with each other and those two tables must have a common column for binding the tables.
2. The common column that is present in both the tables need not have the same name but their data type must be the same.
3. The common column that is present under the parent table or master table is known as the reference key column and moreover, the reference key column should not contain any duplicate values. So, we need to impose either UNIQUE or PRIMARY key constraint on that column.
4. The common column which is present in the child or detailed table is known as the Foreign key column and we need to impose a Foreign key constraint on the column which refers to the reference key column of the master table.

If the above points are not clear at the moment, then don’t worry, we will try to explain all the above points with examples.

Examples to understand FOREIGN KEY Constraints in MySQL.

Let us understand how to create the primary key and foreign key relationship between two tables in MySQL. First, create a table with the name Department by using the PRIMARY KEY constraint by executing the below CREATE Table query. This table is going to be the parent table or master table which contains the reference key column. Here, we created the reference column (Id) using the Primary Key constraint.

CREATE DATABASE EmployeeDB;
USE EmployeeDB;

CREATE TABLE Department
( 
    Id   INT PRIMARY KEY, 
    Name VARCHAR(50), 
    Location  VARCHAR(50) 
);

Once you created the Department table, now insert some master data into this table by executing the below SQL Script.

INSERT INTO Department (Id, Name, Location) VALUES (10, 'IT', 'Hyderabad');
INSERT INTO Department (Id, Name, Location) VALUES (20, 'HR', 'Delhi');
INSERT INTO Department (Id, Name, Location) VALUES (30, 'Finance', 'Mumbai');

Now create another table with the name Employee by using the FOREIGN KEY constraint. This table is going to be our foreign ley table. Further, if you notice in the below query, we have marked the DepartmentId column as the FOREIGN KEY which referencing the Id column of the Department table.

CREATE TABLE Employee 
( 
    ID    INT PRIMARY KEY, 
    Name  VARCHAR(30), 
    Salary INT, 
    DepartmentId    INT,
    FOREIGN KEY (DepartmentId) REFERENCES Department(Id) 
);

Once you execute the above CREATE Table Statements, the Employee table is created with the Foreign Key Constraints. You can verify the same by exploring the Foreign Keys folder as shown in the below image.

Note: The DepartmentId column of the Employee table and Id column of the Department table must have the same data type otherwise foreign key relationship is not possible. In our case both columns having the same INT data type.

INSERT into Employee VALUES (101, 'Anurag', 25000, 10); 
INSERT into Employee VALUES (102, 'Pranaya', 32000, 20); 
INSERT into Employee VALUES (103, 'Hina', 35000, 30);

When you execute the above insert statements, three records are inserted into the Employee table as expected. Now, try to execute the following INSERT Statement. Here, we are passing the Department Id as 40 which actually does not exist in the Department table.

INSERT into Employee VALUES (104, ‘Sambit’, 52000, 40);

When you try to execute the above INSERT SQL Statement, you will get the following error. It clearly says that you cannot add or update a child row if the foreign key value does not exist in the parent table. In this case, the foreign key value i.e. 40 that we want to insert in the employee table does not exist in the Department table and hence we got the below error.

Rules to Follow while working with Foreign Key in MySQL:

When we impose Foreign Key constraint and establish the relation between tables in MySQL, the following 3 rules come into the picture
Rule1: We cannot insert a value into the foreign key column if that value is not existing in the reference key column of the parent (master) table.
Example: INSERT into Employee VALUES (104, ‘Sambit’, 52000, 40);
When we try to execute the above INSERT Statement, you will get the following error.
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`employeedb`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`DepartmentId`) REFERENCES `department` (`Id`))

Rule2: We cannot update the reference key value of a parent table if that the value has a corresponding child record in the child table without addressing what to do with the child records.
Example: UPDATE Department SET Id = 100 WHERE Id = 10;
As we have a child record with department id 10 in the Employee table, so, trying to update the same in the Department table will give you the following error.
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`employeedb`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`DepartmentId`) REFERENCES `department` (`Id`))

Rule3: We cannot delete a record from the parent table provided that the records reference key value has a child record in the child table without addressing what to do with the child record.
Example: DELETE FROM Department WHERE Id = 10;
As we have a child record with department id 10 in the Employee table, so, trying to delete the same in the Department table will give you the following error.
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`employeedb`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`DepartmentId`) REFERENCES `department` (`Id`))

How to add Foreign Key Constraints to Existing Table?

To understand this, let us first, create a table without Foreign Key by executing the below SQL Statement.

CREATE TABLE Employee1 
( 
    ID    INT PRIMARY KEY, 
    Name  VARCHAR(30), 
    Salary INT, 
    DepartmentId    INT
);

Now, once the table is created, we want to add a Foreign Key on the DepartmentId column which should refer to the Id column of the Department table. You can do the same by using the ALTER TABLE statement as shown in the below example.

ALTER TABLE Employee1 ADD FOREIGN KEY (DepartmentId) REFERENCES Department(Id);

How to Delete a Foreign Key Constraints in MySQL?

MySQL allows the ALTER TABLE statement to remove an existing foreign key from the table. The following syntax is used to drop a foreign key in MySQL.

ALTER TABLE table_name DROP FOREIGN KEY fk_constraint_name;

Here, the table_name is the name of a table from where we are going to remove the foreign key. The constraint_name is the name of the foreign key that was added during the creation or alteration of a table. If you don’t know the name of an existing foreign key, then execute the following command.

SHOW CREATE TABLE Employee1;

Once you execute the above query, it will give you the following output.

Now, to delete this foreign key constraint from the Employee1 table, execute the following statement.

ALTER TABLE Employee1 DROP FOREIGN KEY employee1_ibfk_1;

What is the difference between Primary Key and Foreign Key Constraint in MySQL?

Primary Key in MySQL:

1. The Primary Key Constraint in MySQL is uniquely identifying a record in a table.
2. Primary Key constraint neither accepts null values nor duplicate values on the column on which it is applied.
3. We can create only one Primary Key on a table in MySQL and that primary key constraint can be created either on a single column or multiple columns.

Foreign Key in MySQL:

1. The Foreign Key in MySQL is a field in a table that is a unique key (either primary or unique key) in another table.
2. A Foreign Key can accept both null values and duplicate values.
3. We can create more than one Foreign key on a table in MySQL.

Can we create a table with multiple unique, foreign, or primary keys in MySQL?

We can create a table in MySQL with multiple unique and foreign keys. But it is not possible to create a table with multiple primary Keys.

Is it possible that a foreign key references a non-primary key in MySQL?

Yes, it is possible. The point that you need to keep in mind is that a foreign key actually references a key that should contain unique values. So, it may be a primary key or a unique key as both keys maintain the uniqueness of the column of a table.

Can a foreign key accept null values in MySQL?

Yes, a foreign key in MySQL can accept NULL values. This is because a Foreign key can reference unique or non-primary keys which may hold NULL values.

In the next article, I am going to discuss the Referential Integrity Constraint in MySQL with Examples. Here, in this article, I try to explain FOREIGN KEY Constraint in MySQL with Examples and I hope you enjoy this article.