MySQL Foreign Key
The 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. It identifies each row of another table uniquely that maintains the referential integrity in MySQL.
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.
MySQL defines the foreign key in two ways:
Following are the basic syntax used for defining a foreign key using CREATE TABLE OR ALTER TABLE statement in the MySQL:
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.
NOTE: MySQL mainly provides full support to CASCADE, RESTRICT, and SET NULL actions. If we have not specified the ON DELETE and ON UPDATE clause, MySQL takes default action RESTRICT.
Foreign Key Example
Let us understand how foreign key works in MySQL. So first, we are going to create a database named "mysqltestdb" and start using it with the command below:
Next, we need to create two tables named "customer" and "contact" using the below statement:
Table Structure Verification
Here, we are going to see how our database structure looks like using the following queries:
We will get the structure as below:
In the above output, we can see that the PRI in the key column of the customer table tells that this field is the primary index value. Next, the MUL in the key column of the contact value tells that the Customer_Id field can store multiple rows with the same value.
Insert Data to the Table
Now, we have to insert the records into both tables. Execute this statement to insert data into table customer:
After insertion, execute the SELECT TABLE command to check the customer table data as below:
Execute the below insert statement to add data into a table contact:
Our contact table looks like as below:
Now, let's see how foreign keys in MySQL preserve data integrity.
So here, we are going to delete the referential data that removes records from both tables. We have defined the foreign key in the contact table as:
It means if we delete any customer record from the customer table, then the related records in the contact table should also be deleted. And the ON UPDATE CASCADE will updates automatically on the parent table to referenced fields in the child table(Here, it is Customer_Id).
Execute this statement that deletes a record from the table whose name is JOHN.
Again, if we look at our tables, we can see that both tables were changed. It means the fields with name JOHN will be removed entirely from both tables.
Now, test the ON UPDATE CASCADE. Here, we are going to update the Customer_Id of Mary in the contact table as:
Again, if we look at our tables, we can see that both tables were changed with Customer_Id of Mary=3.
Foreign Key example using SET NULL action
Here, we are going to understand how the SET NULL action works with a foreign key. First, we have to create two table named Persons and Contacts, as shown below:
Next, we need to insert the data into both tables using the following statement:
Now, update the ID of the "Persons" table:
Finally, verify the update using the SELECT statement given below:
If we look at our tables, we can see that both tables were changed. The rows with a Person_Id=3 in the Contacts table automatically set to NULL due to the ON UPDATE SET NULL action.
How to DROP Foreign Key
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:
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 of a table.
If we have not known the name of an existing foreign key into the table, execute the following command:
It will give the output as below where we can see that the table contact has one foreign key named fk_customer shown in the red rectangle.
Now, to delete this foreign key constraint from the contact table, execute the statement as below:
We can verify whether foreign key constraint removes or not, use the SHOW CREATE TABLE statement. It will give the output as below where we can see that the foreign key is no longer available in the table contact.
Define Foreign Key Using ALTER TABLE Statement
This statement allows us to do the modification into the existing table. Sometimes there is a need to add a foreign key to the column of an existing table; then, this statement is used to add the foreign key for that column.
Following are the syntax of the ALTER TABLE statement to add a foreign key in the existing table:
When we add a foreign key using the ALTER TABLE statement, it is recommended to first create an index on the column(s), which is referenced by the foreign key.
The following statement creates two tables, "Person" and "Contact", without having a foreign key column into the table definition.
After creating a table, if we want to add a foreign key to an existing table, we need to execute the ALTER TABLE statement as below:
Foreign Key Checks
MySQL has a special variable foreign_key_cheks to control the foreign key checking into the tables. By default, it is enabled to enforce the referential integrity during the normal operation on the tables. This variable is dynamic in nature so that it supports global and session scopes both.
Sometimes there is a need for disabling the foreign key checking, which is very useful when:
The following statement allows us to disable foreign key checks:
The following statement allows us to enable foreign key checks: