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:
- Using CREATE TABLE Statement
- Using ALTER TABLE Statement
Syntax
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.
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:
mysql> CREATE DATABASE mysqltestdb;
mysql> use mysqltestdb; Next, we need to create two tables named “customer” and “contact” using the below statement:
Table: customer
CREATE TABLE customer (
ID INT NOT NULL AUTO_INCREMENT,
Name varchar(50) NOT NULL,
City varchar(50) NOT NULL,
PRIMARY KEY (ID)
); Table: contact
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
);Table Structure Verification
Here, we are going to see how our database structure looks like using the following queries:
mysql> SHOW TABLES;
mysql> DESCRIBE customer;
mysql> DESCRIBE contact; 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:
INSERT INTO customer(Name, City) VALUES
('Joseph', 'California'),
('Mary', 'NewYork'),
('John', 'Alaska'); 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:
INSERT INTO contact (Customer_Id, Customer_Info, Type) VALUES
(1, '[email protected]', 'email'),
(1, '121-121-121', 'work' ),
(1, '123-123-123', 'home'),
(2, '[email protected]', 'email'),
(2, '[email protected]', 'email'),
(2, '212-212-212', 'work'),
(3, '[email protected]', 'email'),
(3, '313-313-313', 'home'); 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:
FOREIGN KEY (Customer_Id) REFERENCES customer(ID)
ON DELETE CASCADE
ON UPDATE CASCADE. 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.
mysql> DELETE FROM customer WHERE Name='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:
mysql> UPDATE customer SET id=3 WHERE Name='Mary'; 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:
Table: Persons
CREATE TABLE Persons (
ID INT NOT NULL AUTO_INCREMENT,
Name varchar(50) NOT NULL,
City varchar(50) NOT NULL,
PRIMARY KEY (ID)
);Table: Customers
CREATE TABLE Contacts (
ID INT,
Person_Id INT,
Info varchar(50) NOT NULL,
Type varchar(50) NOT NULL,
INDEX par_ind (Person_Id),
CONSTRAINT fk_person FOREIGN KEY (Person_Id)
REFERENCES Persons(ID)
ON DELETE SET NULL
ON UPDATE SET NULL
);Next, we need to insert the data into both tables using the following statement:
INSERT INTO Persons(Name, City) VALUES
('Joseph', 'Texas'),
('Mary', 'Arizona'),
('Peter', 'Alaska'); INSERT INTO Contacts (Person_Id, Info, Type) VALUES
(1, '[email protected]', 'email'),
(1, '121-121-121', 'work' ),
(2, '[email protected]', 'email'),
(2, '212-212-212', 'work'),
(3, '[email protected]', 'email'),
(3, '313-313-313', 'home'); Now, update the ID of the “Persons” table:
mysql> UPDATE Persons SET ID=103 WHERE ID=3; 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:
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 of a table.
If we have not known the name of an existing foreign key into the table, execute the following command:
mysql> SHOW CREATE TABLE contact; 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:
mysql> ALTER TABLE contact DROP FOREIGN KEY fk_customer; 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.
Syntax
Following are the syntax of the ALTER TABLE statement to add a foreign key in the existing table:
ALTER TABLE table_name
ADD [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (column_name, ...)
REFERENCES table_name (column_name,...)
ON DELETE referenceOption
ON UPDATE referenceOption 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.
Example
The following statement creates two tables, “Person” and “Contact“, without having a foreign key column into the table definition.
Table: Person
CREATE TABLE Person (
ID INT NOT NULL AUTO_INCREMENT,
Name varchar(50) NOT NULL,
City varchar(50) NOT NULL,
PRIMARY KEY (ID)
); Table: Contact
CREATE TABLE Contact (
ID INT,
Person_Id INT,
Info varchar(50) NOT NULL,
Type varchar(50) NOT NULL
); 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:
ALTER TABLE Contact ADD INDEX par_ind ( Person_Id );
ALTER TABLE Contact ADD CONSTRAINT fk_person
FOREIGN KEY ( Person_Id ) REFERENCES Person ( ID ) ON DELETE CASCADE ON UPDATE RESTRICT; 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:
- We drop a table that is a reference by the foreign key.
- We import data from a CSV file into a table. It speeds up the import operation.
- We use ALTER TABLE statement on that table which has a foreign key.
- We can execute load data operation into a table in any order to avoid foreign key checking.
The following statement allows us to disable foreign key checks:
SET foreign_key_checks = 0; The following statement allows us to enable foreign key checks:
SET foreign_key_checks = 1;
Leave a Reply