Updated May 16, 2023
Introduction to MySQL Foreign Key
Foreign Key is a combination of a single column or group of columns in a table that links to a single or group of columns in another table. The foreign key provides constraints on data in a related table, which allows to main referential Integrity.
Let us see an example of the same.
- Here is what we defined, and we can see that in the above diagram, we can say that the “Loan” table has one- to – one relationship with the “Borrower” table. And here “Loan_No” column from the “Loan” table acts as the Primary key in this table. But serves as a “Foreign Key”.
- A table can have more than one foreign key, the references for the primary key of different tables.
- Here “Loan” table is the parent table (Referenced table), and the “Borrower” acts as the child table (referencing table).
Syntax and Parameters
Below are the syntax and parameters:
Syntax:
create table<Table_name>
(
column1 <data_type>,
column2 <data_type,
.
.
[CONSTRAINT <constraint_name>]
FOREIGN KEY(column_name,...) / * - - Foreign Key Declaration --*/
REFERENCES parent_table(colunm_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
) ENGINE=INNODB;Here reference_option is nothing, but we specify the action that should occur when parent key values are deleted or updated.
MySQL has five reference options:
- Cascade: Cascade is nothing, but whatever action has been taken in the parent table should reflect the same in the child table. For example, if a row from the parent table is updated or deleted, the values of the same matching rows in the child table should reflect the same.
- Set NULL: Unlike Cascade, if you don’t want to delete the corresponding values in the child table, instead if you are willing to set the values to NULL, then we use “SET NULL”. For example, if a change has been done in the parent table, the matching rows in the child table will be set to “NULL”.
- No Action: No Action by the name itself says that No action will be taken in the parent table of updating or deleting the rows if matching rows are present in the child table.
- Restrict: Restrict works the same as the “No Action”.
- Set Default: This is recognized by the MySQL parser. However, this action is rejected by both InnoDB and NDB tables.
How does MySQL Foreign Key work?
Now let us create a table with a foreign key and see how it works: –
1. With “No Action” and “Restrict”
Code:
LOAN_DETAILS:
CREATE TABLE LOAN_DETAILS
(
LOAN_NO INT PRIMARY KEY,
LOAN_STATUS VARCHAR(20),
STATE VARCHAR(20),
LOAN_AMOUNT INT,
AP_DATE DATETIME
) ENIGINE = INNODB;
Insert data into the above table: -
INSERT INTO LOAN_DETAILS VALUES (12,'Open','Ranchi','30000','01-02-2015');
INSERT INTO LOAN_DETAILS VALUES (43,'Approval','Electronic city','54000','10-11-2019');
INSERT INTO LOAN_DETAILS VALUES (31,'Pending','Kolkata','80000','09-07-2018');
INSERT INTO LOAN_DETAILS VALUES (23,'Close','Patna','50000','03-04-2017');Output:
BORROWER_DETAILS
CREATE TABLE BORROWER_DETAILS
(
BORROWER_ID INT PRIMARY KEY,
BORROWER_NAME VARCHAR(20),
BORROWER_DATE DATE,
BANK_ID VARCHAR(5),
LOAN_NO INT,
CONSTRAINT FK_BORROWER_DETAILS
FOREIGN KEY (LOAN_NO)
REFERENCES LOAN_DETAILS(LOAN_NO)
) ENGINE=INNODB;Insert data into the above table:
Here let us try to insert data of loan_no that are already present in the “Loan_details” parent table.
Code:
INSERT INTO BORROWER_DETAILS VALUES (11,'Ram','09-07-2014','a1', 12);
INSERT INTO BORROWER_DETAILS VALUES (12,'Sundar','01-13-2019','a2', 43);
INSERT INTO BORROWER_DETAILS VALUES (13,'Will','09-01-2018','a1', 31);Output:
Now let us try inserting the row data of ‘loan_no’, which is not in the “loan_details” table.
INSERT INTO BORROWER_DETAILS VALUES (15,'Raju','09-17-2014','a4', 67);Here we don’t have any “loan_no” of 67 in the parent table, i.e., so it throws the error:
Output:
Now let us try to update the loan_no from the parent table “LOAN_DETAILS”.
update LOAN_DETAILS set LOAN_NO=87 where LOAN_NO=31;- As loan_no= 31 is present in the child table, we can’t edit or delete the row. Hence it pops up an error.
- As “No action” and “Restrict” are here, no action will be taken in the parent table of updating or deleting the rows if matching rows are present in the child table.
2. CASCADE
To create the cascade statement in the table, we have dropped the table “Borrower_details” created with the below statement.
DROP TABLE BORROWER_DETAILS;Now let us create the table “BORROWER_DETAILS” WITH the CASCADE action.
Code:
CREATE TABLE BORROWER_DETAILS
(
BORROWER_ID INT PRIMARY KEY,
BORROWER_NAME VARCHAR(20),
BORROWER_DATE DATE,
BANK_ID VARCHAR(5),
LOAN_NO INT,
CONSTRAINT FK_BORROWER_DETAILS
FOREIGNKEY (LOAN_NO)
REFERENCES LOAN_DETAILS(LOAN_NO)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=INNODB;Output:
Insert data into the table: –
INSERT INTO BORROWER_DETAILS VALUES (11,'Ram','09-07-2014','a1', 12);
INSERT INTO BORROWER_DETAILS VALUES (12,'Sundar','01-13-2019','a2', 43);
INSERT INTO BORROWER_DETAILS VALUES (13,'Will','09-01-2018','a1', 31);
Now let us try to update the loan_no from the parent table “LOAN_DETAILS”.
update LOAN_DETAILS set LOAN_NO=100 where LOAN_NO=31;As loan_no= 31 is present in the child table when we update or delete the row. It updates in the child table as well.
Now let us select the child table “borrower_details” to check if the updates have been done. It has been updated in the child table as well.
Code:
select * from BORROWER_DETAILS;Output:
Now let us try to delete the row with “Loan_no=100” and check the output.
Code:
delete from LOAN_DETAILS where LOAN_NO=100;Output:
Select the table “borrower_details”
Code:
select * from BORROWER_DETAILS;Output:
3. SET NULL Action
To create the SET NULL statement in the table, we have dropped the table “Borrower_details” created with the below statement.
DROP TABLE BORROWER_DETAILS;Now let us create the table “BORROWER_DETAILS” WITH the CASCADE action.
CREATE TABLE BORROWER_DETAILS
(
BORROWER_ID INT PRIMARY KEY,
BORROWER_NAME VARCHAR(20),
BORROWER_DATE DATE,
BANK_ID VARCHAR(5),
LOAN_NO INT,
CONSTRAINT FK_BORROWER_DETAILS
FOREIGNKEY (LOAN_NO)
REFERENCES LOAN_DETAILS(LOAN_NO)
ON UPDATE SET NULL
ON DELETE SET NULL
) ENGINE=INNODB;
Here let us try to insert data of loan_no that are already present in the “Loan_details” parent table.
INSERT INTO BORROWER_DETAILS VALUES (11,'Ram','09-07-2014','a1', 12);
INSERT INTO BORROWER_DETAILS VALUES (12,'Sundar','01-13-2019','a2', 43);
INSERT INTO BORROWER_DETAILS VALUES (13,'Will','09-01-2018','a1', 23);
Below are the rows that are present in the table “loan_details: –
Code:
select * from LOAN_DETAILS;Output:
Now let us perform the update and delete option.
Let us update the loan_no=12 to loan_no=1000.
update LOAN_DETAILS set LOAN_NO=1000 where LOAN_NO=12;
Now let us select the “BORROWER_DETAILS” table:
Code:
select * from BORROWER_DETAILS;Output:
SET NULL will set the foreign key’s value to NULL on performing deletion or updation of the matched column in the parent table.
Now let us perform the deletion operation from the parent table where loan_no=43.
Code:
delete from LOAN_DETAILS where LOAN_NO=43;Output:
Let us select the child table “borrower_details”
Code:
select * from BORROWER_DETAILS;Output:
Examples to Implement MySQL Foreign Key
Now let us create a table to see how the foreign key works:
CREATE TABLE TEACHER_D
(
T_ID INT PRIMARY KEY,
T_NAME VARCHAR(20),
T_SUBJECT VARCHAR(20)
) ENGINE =INNODB;Teacher subject details:
CREATE TABLE TEACHER_SUBJECT_DETAILS
(
Sub_ID INT PRIMARY KEY,
No_of_students INT,
T_ID INT,
CONSTRAINT FK_TEACHER_DETAILS
FOREIGN KEY (T_ID)
REFERENCES TEACHER_D(T_ID)
ON UPDATE SET NULL
ON DELETE CASCADE
) ENGINE =INNODB;Here we have created the child table with two actions. If we update the parent table, the child table will set the value to “NULL”. If we perform the Delete operation on the parent table, the same will also be performed in the child table.
Insert data into the tables: –
insert into teacher_d values ( 1,'Swathi','Physics');
insert into teacher_d values ( 2,'Preethi','Chemistry');
insert into teacher_d values ( 3,'Prem','Mathematics');
insert into teacher_subject_details values (214, 56, 1);
insert into teacher_subject_details values (215, 89, 2);
insert into teacher_subject_details values (216, 100, 3);
If we update the parent table, the child table will set the value to “NULL”. Let us update the row of value T_ID = 1 to T_ID = 1000; It updates the parent table “Teacher_D” and sets the value to NULL in the child table “teacher_subject_details”
Code:
UPDATE TEACHER_D SET T_ID=1000 WHERE T_ID=1;
Before the update statement output:
SELECT * FROM TEACHER_D;
SELECT * FROM TEACHER_SUBJECT_DETAILS;
After the update statement output:
SELECT * FROM TEACHER_D;
SELECT * FROM TEACHER_SUBJECT_DETAILS;
If we perform delete on the parent table, the child table will also be deleted, as we have mentioned CASCADE action on deletion. Let us delete the row of value T_ID = 2 from the parent table “Teacher_D” and we can see that the row will be deleted in the child table “teacher_subject_details”.
Before deleting statement execution output:
SELECT * FROM TEACHER_D;
SELECT * FROM TEACHER_SUBJECT_DETAILS;
Now let us perform the deletion operation as below: –
delete from teacher_d where t_id= 2;.
After the deletion statement is performed, output:
We could see in the output that the row of T_ID=2 has also been deleted from the parent and child table.
SELECT * FROM TEACHER_D;
SELECT * FROM TEACHER_SUBJECT_DETAILS;A screenshot is for the same:
Conclusion
Things that need to be taken care of: –
Foreign Key is a combination of a single column or group of columns in a table that links to a single or group of columns in another table. The foreign key provides constraints on data in related tables, allowing main referential Integrity. A table can have more than one foreign key, which refers to the primary key of different tables.
Recommended Articles
We hope that this EDUCBA information on “MySQL Foreign Key” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
