Basics Of MySQL FOREIGN KEY Constraint With Examples

This tutorial explains the basics of MySQL FOREIGN KEY Constraint such as its syntax, how to add, declare, drop, and change it with examples:

In very simple terms, the FOREIGN KEY is used to link two or more tables in MySQL.

MySQL tables need to be connected in order to query and update various types of data at different points in time. Hence, it is imperative to have a linking point between 2 tables.

In this tutorial, we will discuss different usages of Foreign Keys and how can it be declared and changed, and what constraints it has on the overall table structure.

=> Click here for the complete MySQL tutorial series

MySQL FOREIGN KEY

Syntax:

CONSTRAINT {constraintName} 
	FOREIGN KEY (referringColumnName)
	REFERENCES {referredTable}({referredColumn})
	ON UPDATE {reference-option}
	ON DELETE {reference-option}

Above is the syntax used when specifying FOREIGN KEY against a table while table creation or with ALTER TABLE statement.

Let’s understand the different components of the syntax:

• constrantName: This is the symbolic name we want to define for the FK constraint being specified. If this is skipped then the MySQL engine auto assigns a name to the FK constraint.
• referringColumnName: This is the column that would refer to the values in another table as specified by the column in the referred table.
• Referred table/parent table: This refers to the name of the table from which the values would be referred.
• Referred Column: The column name in the referred table.
  
• Reference option: These are the actions that come into the picture when an update or delete action is done on the table that holds the foreign key constraint. Both UPDATE and DELETE can have the same or different reference options.

We would learn about different referential integrity actions later in this tutorial.

Let’s see an example of a FOREIGN KEY reference using the Employee/Department example. We will create a table Department with columns – departmentId (int & PRIMARY KEY) and departmentName (varchar).

CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL, departmentName VARCHAR(100));

Create a table Employee with columns as below:

CREATE TABLE employee(id INT PRIMARY KEY NOT NULL,  name VARCHAR(100),

address VARCHAR(100), age INT, dob DATE, deptId INT,
CONSTRAINT depIdFk FOREIGN KEY (deptId)
REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE);

As you can see, in the above Employee table, we have declared deptId column of type Int and defined FOREIGN KEY from the Department table on column departmentId.

What this essentially means that deptId column in the Employee table can contain only values that are in the Department table.

Let’s try inserting data into these tables and see how FOREIGN KEY CONSTRAINT works.

• Create a record in the Department table first and add a record in the Employee table referencing the ID of the record that was added to the Department table.

INSERT INTO department VALUES (1, 'ENGINEERING')
---------
INSERT INTO EMPLOYEE VALUES (1, 'AMIT KUMAR', 'MUMBAI', 32, '1988-02-12',1);

You will see that both the statements would get executed without errors.

• Now reference a value for departmentId which is not existing.
  For example, in the below query statement, we are creating an Employee with a non-existing departmentId -10

INSERT INTO EMPLOYEE VALUES (1, 'DARREN JOHNSON', 'CHICAGO', 32, '1988-02-12',10);

• In this scenario, we will get an error like below:

Error Code: 1452. Cannot add or update a child row: a foreign key
 constraint fails (`my_sql_foreign_key`.`employee`, CONSTRAINT
 `depIdFk` FOREIGN KEY (`deptId`) REFERENCES `department` (`departmentId`)
ON DELETE CASCADE ON UPDATE CASCADE)

So, broadly, when FOREIGN KEY References are defined, it is important to ensure that the table that is being referenced should have data before it’s being referred.

Referential Integrity Actions

Let’s first try to understand what exactly is Referential Integrity.

Referential Integrity helps to maintain data in a clean and consistent state where there are tables related to each other with a FOREIGN KEY relationship.

Simply stated, Referential Integrity refers to the Action that we expect from the database engine to take, when an UPDATE or DELETE happens in the referenced table that contains the FOREIGN KEY.

For example, in our Employee/Department example, suppose we change the Department ID for a certain row in DB. Then all the referencing rows in the Employee table would be impacted. We can define different types of Referential Integrity scenarios that could be applied during such cases.

Note: Referential Integrity is defined during FOREIGN KEY setup/declaration as part of the ON DELETE and ON UPDATE commands/sections.

Refer to a sample query here (for the Employee/Department example):

CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL, 
departmentName VARCHAR(100));

CREATE TABLE employee(id INT PRIMARY KEY NOT NULL,  name VARCHAR(100), 
address VARCHAR(100), age INT, dob DATE, deptId INT,
			CONSTRAINT depIdFk FOREIGN KEY (deptId)
                        REFERENCES department(departmentId)
                        ON UPDATE {ref-integrity-action}
                        ON DELETE {ref integrity action});

Insert some data to these tables as below:

INSERT INTO department VALUES (1, 'ENGINEERING'),
					               (2,'ACCOUNTING'),
                                                      (3, 'MARKETING'),
                                                      (4, 'HR'),
                                                      (5, 'LEGAL');

INSERT INTO EMPLOYEE VALUES 
(1, 'AMIT KUMAR', 'MUMBAI', 32,'1988-02-12',1),
(2, 'RYAN HILMAN', 'SEATTLE',43, '1977-03-15',1),
(3, 'KAVITA SINGH', 'DELHI', 42, '1978-02-18',4),
(4, 'DAVID BECKHAM', 'LONDON', 40, '1980-07-13',3),
(5, 'PRITI KUMARI', 'DELHI', 35, '1985-12-11',2),
(6, 'FRANK BALDING', 'NEW YORK', 35, '1985-08-25',5)

There are 4 Reference Actions that are supported by MySQL. Let’s try to understand each one of them.

#1) CASCADE

This is one of the most commonly used Referential Integrity Actions. Setting DELETE and UPDATE to CASCADE would apply the changes done to the referenced table on the referencing table, i.e. in the Employee/Department example. Suppose someone deletes a row in the Department table having to say department_name=ACCOUNTING, then all rows in the Employee table having department_id as that of Accounting table would be deleted as well.

Let’s understand this with an example:

SELECT * FROM employee;

Delete record from the Department table where departmentName =’ACCOUNTING’

DELETE from DEPARTMENT WHERE departmentName='ACCOUNTING';

Now, since it’s a CASCADE Referential action, we would expect all the rows that have departmentID = 2 (which is for ‘ACCOUNTING’ department) should be deleted as well. Let’s do a SELECT query on the Employee table again.

SELECT * FROM employee;

As you can see above, due to CASCADE referential integrity, the rows in the Employee table that referred to the deleted column as FOREIGN KEY will have those rows deleted.

#2) RESTRICT/NO ACTION

RESTRICT or NO ACTION mode will not allow any UPDATE or DELETE operations on the table having columns that are being referenced as FOREIGN KEY in some table.
The NO ACTION mode can be applied by simply omitting the ON UPDATE and ON DELETE clauses from the table declaration.

Let’s try the same example and in this case simply skip the ON UPDATE and ON DELETE Referential integrity action.
Now when we try to delete any entry in the referenced table, we would get an error as we have set the referential action to RESTRICT

DELETE FROM department WHERE departmentName='ACCOUNTING';

You will see an error something like below if you try to execute the above DELETE command.

Error Code: 1451. Cannot delete or update a parent row: a foreign 
key constraint fails (`my_sql_foreign_key`.`employee`, CONSTRAINT
 `depIdFk` FOREIGN KEY (`deptId`) REFERENCES `department` (`departmentId`))

#3) SET NULL

With SET NULL, any UPDATE or DELETE in the referenced table would cause a NULL value to be updated against column value that’s marked as a FOREIGN KEY in the referencing table.

With this referential integrity action, the definition of the Employee table would become as below:

CREATE TABLE employee(id INT PRIMARY KEY NOT NULL,  name 

VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT,
CONSTRAINT depIdFk FOREIGN KEY (deptId)
REFERENCES department(departmentId)
ON DELETE SET NULL);

Delete a row in the referenced table as shown below:

DELETE FROM department WHERE departmentName='ACCOUNTING';

Now, in this case, the referenced value in the Employee table would be set to NULL. Do a SELECT query on the Employee table to see the results.

SELECT * FROM employee;

#4) SET DEFAULT

SET DEFAULT mode when specified would result in substituting the default value for the column (as specified during the column declaration), in the event of any DELETES in the table being referenced.

Note – As per MySQL Documentation, the SET DEFAULT option is supported by MySQL Parser but not DB engines like InnoDB. This might be supported in future.

However, to support such behavior, you can consider using SET NULL and define a trigger on the table which could set a default value.

Add FOREIGN KEY Constraint Using ALTER TABLE Statement

A lot of times it may happen that we might want to add a FOREIGN KEY constraint to an existing table that does not have it.

Suppose in the Employee and Department example, we created an employee table without any FOREIGN KEY constraint and later we want to introduce the constraint. This can be achieved using the ALTER TABLE command.

Let’s try to understand this with an example.

Suppose we have an Employee table with the below definition for CREATE command.

CREATE TABLE employee(id INT PRIMARY KEY NOT NULL,  name VARCHAR(100), 
address VARCHAR(100), age INT, dob DATE, deptId INT);

Here, we have a column deptId but no FOREIGN KEY constraint. In this case, even without having a Department table, we can specify any values while inserting records.

Now, later suppose we have a separate Department table and we want to link departmentId there as FOREIGN KEY to the Employee table.

ALTER TABLE employee
ADD CONSTRAINT depIdFk FOREIGN KEY (deptId)
REFERENCES department(departmentId)
ON UPDATE CASCADE ON DELETE CASCADE;

What if this table has existing data? Can we ALTER table and add FOREIGN KEY constraint?

The answer is yes – we can with the condition that the existing values in the column which are going to be referenced from another table should have those values existing in the parent table itself.

Create an Employee table without FOREIGN KEY constraint, add some data, and try adding a FOREIGN KEY constraint using ALTER command.

CREATE TABLE employee(id INT PRIMARY KEY NOT NULL,  name VARCHAR(100), 
address VARCHAR(100), age INT, dob DATE, deptId INT);

INSERT INTO EMPLOYEE VALUES (1, 'DARREN JOHNSON', 'CHICAGO', 32, '1988-02-12',1);
INSERT INTO EMPLOYEE VALUES (2, 'ANITA SHERWIN', 'COLUMBIA', 32, '1988-02-12',10);

Create a Department table and add FOREIGN KEY against the ‘deptId’ field in the Employee table as shown below:

CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL DEFAULT 
1000, departmentName VARCHAR(100));

At this point, if we try adding FOREIGN KEY constraint ,

ALTER TABLE employee
ADD CONSTRAINT depIdFk FOREIGN KEY (deptId)
REFERENCES department(departmentId)
ON UPDATE CASCADE ON DELETE CASCADE;

Then we will get an error, as the Employee table contains some data but the referential integrity constraint cannot be met as the Department table has no data yet.

Error Code: 1452. Cannot add or update a child row: a foreign key
 constraint fails (`my_sql_foreign_key`.`#sql-63_87`, CONSTRAINT
 `depIdFk` FOREIGN KEY (`deptId`) REFERENCES `department` (`departmentId`) 
ON DELETE CASCADE ON UPDATE CASCADE)

In order to have the FOREIGN KEY constraint, we will first need to add data to the Department table. Let’s insert the required records in the Department table.

INSERT INTO department VALUES (1, 'ENGINEERING'),(10,'ACCOUNTING');

Add the FOREIGN KEY constraint again by executing the same ALTER TABLE statement. You will notice that this time, the command is successful and the Employee table is successfully updated to have deptId as FOREIGN KEY from the Department table.

Dropping A FOREIGN KEY Constraint

Similar to adding a FOREIGN KEY constraint, it’s also possible to drop/delete an existing FOREIGN KEY constraint from a table.

This can be achieved using ALTER TABLE command.

Syntax:

ALTER TABLE {childTable} 
DROP FOREIGN KEY {foreign key constraint name};

Here ‘childTable’ is the name of the table which has FOREIGN KEY constraint defined, while the ‘foreign key constraint name’ is the name/symbol which was used to define the FOREIGN KEY.

Let’s see an example using the Employee/Department table. To drop a constraint named ‘depIdFk’ from the Employee table, use the below command:

ALTER TABLE employee DROP FOREIGN KEY depIdFk;

Frequently Asked Questions

Q #1) How can I change foreign keys in MySQL?

Answer: FOREGIN KEY can be added/removed using the ALTER TABLE command.
In order to change or add a new FOREIGN KEY, you can use the ALTER command and define the FOREIGN KEY and referencing table column which would be referenced from the child table.

Q #2) How to set multiple foreign keys in MySQL?

Answer: A table in MySQL can have multiple FOREIGN KEYS, which could depend on the same parent table or different parent tables.

Let’s use the Employee/Department table and add FOREIGN KEY for Department name as well as DepartmentId in the Employee table.

Refer the CREATE statements of both the tables as below

CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL DEFAULT
 1000, departmentName VARCHAR(100) UNIQUE NOT NULL);

----xxxxx------xxxxx------xxxxx-------xxxxx------xxxxx

CREATE TABLE employee(id INT PRIMARY KEY NOT NULL,  name VARCHAR(100), 
address VARCHAR(100), age INT, dob DATE, deptId INT, depName VARCHAR(100),
CONSTRAINT depIdFk FOREIGN KEY (deptId)  
REFERENCES department(departmentId)
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT depNameFk FOREIGN KEY (depName) 
REFERENCES department(departmentName)
ON UPDATE CASCADE ON DELETE CASCADE);

Q #3) How to disable foreign key constraints in MySQL?

Answer: FOREIGN KEY constraints are usually required when someone is trying to truncate an existing table that’s being referenced. In order to do that, you can use the below command:

SET FOREIGN_KEY_CHECKS=0;

This would set a session variable and temporarily disable the FOREIGN_KEY_CHECKS. After this setting, you can go ahead and perform deletes/truncate, which would have been otherwise not possible.

But please ensure that this is an admin privilege and should be used judiciously.

Q #4) How do I find the foreign key references for a table in MySQL?

Answer: In order to list down all the FOREIGN KEY constraints that are present, you can use the ‘INNODB_FOREIGN_COLS’ table in ‘INFORMATION_SCHEMA`.

Simply run the command below to get all FOREIGN KEY declarations existing for a given MySQL server instance.

Q #5) Should the referenced column as FOREIGN KEY be a primary key in the referenced table?

Answer: By definition of FOREIGN KEY, it would be required that the column that’s being referenced as FOREIGN KEY should be the PRIMARY KEY from the table where it’s being referenced.
However, with the newer versions of MySQL and with InnoDB database engine, you could also reference a column that has FOREIGN KEY which has a UNIQUE constraint and may not be necessarily PRIMARY KEY.

Q #6) Does FOREIGN KEY create INDEX in MySQL?

Answer: For both Primary Key and Unique constraint, MySQL automatically creates an INDEX for such columns.

Since we already know that FOREIGN KEY references can only be applied to either column that are Primary keys or columns that have Unique values, hence all the columns that are referred as FOREIGN KEY do have an index created against them.

In order to view the index on a table, use the below command:

SHOW INDEX from {dbName.tableName};

So, for our Employee/Department example, we had added deptId in Employee as a FOREIGN KEY from the Department table.

Let’s see the created indexes in Employee and Department tables.

USE my_sql_foreign_key;
SHOW INDEX from employee;

You can see 2 indexes – one is the primary key for the Employee table and another one is for FOREIGN KEY depId which is referenced from the Department table.

SHOW INDEX from department;

Here you can see that for Department table, we have only 1 index for Primary Key (which is referenced as FOREIGN KEY in Employee table).

Q #7) Can FOREIGN KEY be NULL in MySQL?

Answer: Yes, it’s perfectly ok to have NULL for the column that has a FOREIGN KEY dependency on another table. This also alludes to the fact that NULL is not a real value hence it’s not matched/compared against values in the parent table.

Conclusion

In this tutorial, we learned about different concepts related to using FOREIGN KEYS in MySQL databases.

FOREIGN KEY eases out the updates and deletes with appropriate restrictions, but sometimes having a lot of such relationships might make the entire process of Insertion and/or Deletion quite cumbersome.

We learned how to create FOREIGN KEYS and how we can update and drop an existing FOREIGN KEY from the child table. We also learned about different referential integrity actions and how we can achieve different behavior using the different available options like CASCADE, NO ACTION, SET NULL, etc.