The MySQL Foreign key is used to link one or more than one table together. It is also known as the referencing key. A foreign key matches the primary key field of another table. It means a foreign key field in one table refers to the primary key field of the other table.
A foreign key makes it possible to create a parent-child relationship with the tables. In this relationship, the parent table holds the initial column values, and column values of child table reference the parent column values. MySQL allows us to define a foreign key constraint on the child table.
Following are the basic syntax used for defining a foreign key using CREATE TABLE OR ALTER TABLE statement in the MySQL:
[CONSTRAINT constraint_name] FOREIGN KEY [foreign_key_name] (col_name, ...) REFERENCES parent_tbl_name (col_name,...) ON DELETE referenceOption ON UPDATE referenceOption
In the above syntax, we can see the following parameters:
constraint_name: It specifies the name of the foreign key constraint. If we have not provided the constraint name, MySQL generates its name automatically.
col_name: It is the names of the column that we are going to make foreign key.
parent_tbl_name: It specifies the name of a parent table followed by column names that reference the foreign key columns.
Refrence_option: It is used to ensure how foreign key maintains referential integrity using ON DELETE and ON UPDATE clause between parent and child table.
MySQL contains five different referential options, which are given below:
CASCADE: It is used when we delete or update any row from the parent table, the values of the matching rows in the child table will be deleted or updated automatically.
SET NULL: It is used when we delete or update any row from the parent table, the values of the foreign key columns in the child table are set to NULL.
RESTRICT: It is used when we delete or update any row from the parent table that has a matching row in the reference(child) table, MySQL does not allow to delete or update rows in the parent table.
NO ACTION: It is similar to RESTRICT. But it has one difference that it checks referential integrity after trying to modify the table.
SET DEFAULT: The MySQL parser recognizes this action. However, the InnoDB and NDB tables both rejected this action.
Foreign Key Example
mysql> CREATE DATABASE mysqltestdb; mysql> use mysqltestdb;
Next, we need to create two tables named “customer” and “contact” using the below statement:
CREATE TABLE customer ( ID INT NOT NULL AUTO_INCREMENT, Name varchar(50) NOT NULL, City varchar(50) NOT NULL, PRIMARY KEY (ID)
CREATE TABLE contact ( ID INT, Customer_Id INT, Customer_Info varchar(50) NOT NULL, Type varchar(50) NOT NULL, INDEX par_ind (Customer_Id), CONSTRAINT fk_customer FOREIGN KEY (Customer_Id) REFERENCES customer(ID) ON DELETE CASCADE ON UPDATE CASCADE );