-->

MySQL Foreign Key

Summary: In this chapter, we will learn about MySQL foreign key and how to create, add, and drop foreign key constraints in MySQL.

Introducing to MySQL foreign key

A MySQL foreign key is a key used to link two tables together. The foreign key in a table points to a PRIMARY KEY in another table.
A foreign key is a field in a table that matches another field of another table. A foreign key places constraints on data in the related tables, which enables MySQL to maintain referential integrity.
Let’s take a look at the following database diagram in the sample database.
We have three tables: employees, department and branches. Each employee has zero or more department and each department belongs to only one employee. The relationship between employees table and departments table is one-to-many, and it is established by a foreign key in the department table specified by the departmentid field.
Same relationship apply for employees and branches table also because below database structure having two foreign key relationship.
The employees table is called parent table or referenced table, and the department & branches tables are known as child table or referencing table.
Notice that the departmentid column in the employees table points to the departmentid column in the department table.
  • The departmentid column in the department table is the PRIMARY KEY in the department table.
  • The departmentid column in the employees table is a FOREIGN KEY in the employees table.
Same database relationship apply for employees and branches table also because above database structure having two foreign key relationship.
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

MySQL FOREIGN KEY on CREATE TABLE

MySQL foreign key syntax

The following syntax illustrates how to define a foreign key in a child table in CREATE TABLE statement.
1
2
3
4
5
CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name (COLUMNS)
REFERENCES parent_table(COLUMNS)
ON DELETE ACTION
ON UPDATE ACTION
Let’s examine the syntax in detail:
  1. The CONSTRAINT clause allows you to define constraint name for the foreign key constraint. If you omit it, MySQL will generate a name automatically.
  2. The FOREIGN KEY clause specifies the columns in the child table that refers to primary key columns in the parent table. You can put a foreign key name after FOREIGN KEY clause or leave it to let MySQL create a name for you. Notice that MySQL automatically creates an index with the foreign_key_name name.
  3. The REFERENCES clause specifies the parent table and its columns to which the columns in the child table refer. The number of columns in the child table and parent table specified in the FOREIGN KEY and REFERENCES must be the same.
  4. The ON DELETE clause allows you to define what happens to the records in the child table when the records in the parent table are deleted. If you omit the ON DELETE clause and delete a record in the parent table that has records in the child table refer to, MySQL will reject the deletion.
  5. The ON UPDATE clause enables you to specify what happens to the rows in the child table when rows in the parent table are updated. You can omit the ON UPDATE clause to let MySQL reject any updates to the rows in the child table when the rows in the parent table are updated.

MySQL foreign key on create table example

The following example creates a demo database and two tables: employees and department. Each category has one or more products and each product belongs to only one category. The departmentid field in the employees table is defined as a foreign key with UPDATE ON CASCADE and DELETE ON RESTRICT actions.
First, we will create demo database and use that database
1
2
CREATE DATABASE IF NOT EXISTS demo;
USE demo;
Now we will create department and employees table using following syntax
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
/* Creating department table */
CREATE TABLE `department` (
  `departmentid` INT(11) NOT NULL,
  `departmentname` VARCHAR(50) NOT NULL,
PRIMARY KEY (`departmentid`)
);
/* Creating employees table */
CREATE TABLE `employees` (
  `employeeid` INT(11) NOT NULL,
  `firstname` VARCHAR(50) NOT NULL,
  `lastname` VARCHAR(50) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `jobtitle` VARCHAR(50) NOT NULL,
  `departmentid` INT(11) DEFAULT NULL,
PRIMARY KEY (`employeeid`),
KEY `emp_department_FK` (`departmentid`),
CONSTRAINT `emp_department_FK` FOREIGN KEY (`departmentid`)
                           REFERENCES `department` (`departmentid`)
ON UPDATE CASCADE
ON DELETE RESTRICT
);

MySQL FOREIGN KEY on ALTER TABLE

MySQL adding foreign key syntax

To add a foreign key to an existing table, we use the ALTER TABLE statement with the foreign key definition syntax above:
1
2
3
4
5
6
ALTER table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name(COLUMNS)
REFERENCES parent_table(COLUMNS)
ON DELETE ACTION
ON UPDATE ACTION;

MySQL adding foreign key example

Now, let’s add a new table named branches and change the employees table to include the branchid field:
First, we will create demo database and use that database
1
USE demo;
Now we will create branches table and add one more column branchid to employees table using following syntax
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
/* Creating branches table */
CREATE TABLE `department` (
  `departmentid` INT(11) NOT NULL,
  `departmentname` VARCHAR(50) NOT NULL,
PRIMARY KEY (`departmentid`)
);
/* Add one more column branchid to employees table */
ALTER TABLE branches
ADD COLUMN branchid INT NOT NULL AFTER departmentid;
/* To add a foreign key to the employees table */
ALTER TABLE employees
ADD FOREIGN KEY `emp_branch_FK` (`branchid`),
REFERENCES `branches` (`branchid`)
ON DELETE NO ACTION
ON UPDATE CASCADE;
Now, the employees table has two foreign keys, one refers to the department table and another refers to the branches table.

MySQL dropping foreign key

We also use the ALTER TABLE statement to drop foreign key as the following statement:
1
2
ALTER TABLEtable_name
DROP FOREIGN KEY constraint_name;
In the statement above:
  • First, you specify the table name from which you want to remove the foreign key.
  • Second, you put the constraint name after the DROP FOREIGN KEY clause.
Notice that constraint_name is the name of the constraint specified when you created or added the foreign key to the table. If you omit it, MySQL generates a constraint name for you.
To obtain the generated constraint name of a table, you use the SHOW CREATE TABLE statement as follows:
1
SHOW CREATE TABLE employees;
The following is the output of the statement:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE `employees` (
  `employeeid` INT(11) NOT NULL,
  `firstname` VARCHAR(50) NOT NULL,
  `lastname` VARCHAR(50) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `jobtitle` VARCHAR(50) NOT NULL,
  `departmentid` INT(11) DEFAULT NULL,
  `branchid` int(11) DEFAULT NULL,
PRIMARY KEY (`employeeid`),
KEY `emp_department_FK` (`departmentid`),
KEY `emp_branch_FK` (`branchid`),
CONSTRAINT `emp_department_FK` FOREIGN KEY (`departmentid`)
                           REFERENCES `department` (`departmentid`),
CONSTRAINT `emp_branch_FK` FOREIGN KEY (`branchid`)
                           REFERENCES `branches` (`branchid`)
ON UPDATE CASCADE
ON DELETE RESTRICT
);
We can see above output, employees table has two foreign key constraints: `emp_branch_FK` and `emp_department_FK`
You can drop the foreign keys of the employees table by using the following statement:
1
2
3
4
5
ALTER TABLE employees
DROP FOREIGN KEY `emp_branch_FK`;
  
ALTER TABLE employees
DROP FOREIGN KEY `emp_department_FK`;

MySQL 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 in any orders.
Another example is that, unless you disable the foreign key checks, you cannot drop a table that is referenced by a foreign key constraint. When you drop a table, any constraints that you defined for the table are also removed.
To disable foreign key checks, you use the following statement:
1
SET FOREIGN_KEY_CHECKS = 0;
And of course, you can enable it using the statement below:
1
SET FOREIGN_KEY_CHECKS = 1;
In this chapter, we have covered a lot about MySQL foreign key. We also introduced you to some very handy statements that allow you to manage foreign keys effectively in MySQL.
MySQL Primary Key MySQL Sequence

at
Labels: