EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Foreign Key 
 

MySQL Foreign Key 

Roja Metla
Article byRoja Metla
EDUCBA
Reviewed byRavi Rathore

Updated May 16, 2023

MySQL-Foreign-Key

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Let us see an example of the same.

loan

  • 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:

No Action

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:

Restrict

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:

loan_no

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.

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:

BORROWER_DETAILS

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);

MySQL Foreign Key  - 7

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.

MySQL Foreign Key  - 8

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:

MySQL Foreign Key  - 9

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:

MySQL Foreign Key  - 10

Select the table “borrower_details”

Code:

select * from BORROWER_DETAILS;

Output:

MySQL Foreign Key  - 11

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;

MySQL Foreign Key  - 12

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);

MySQL Foreign Key  - 13

Below are the rows that are present in the table “loan_details: –

Code:

select * from LOAN_DETAILS;

Output:

MySQL Foreign Key  - 14

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;

MySQL Foreign Key  - 15

Now let us select the “BORROWER_DETAILS” table:

Code:

select * from BORROWER_DETAILS;

Output:

MySQL Foreign Key  - 16

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:

MySQL Foreign Key  - 17

Let us select the child table “borrower_details”

Code:

select * from BORROWER_DETAILS;

Output:

MySQL Foreign Key  - 18

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);

MySQL Foreign Key  - 19

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;

MySQL Foreign Key  - 20

Before the update statement output:

SELECT * FROM TEACHER_D;
SELECT * FROM TEACHER_SUBJECT_DETAILS;

MySQL Foreign Key  - 21

After the update statement output:

SELECT * FROM TEACHER_D;
SELECT * FROM TEACHER_SUBJECT_DETAILS;

MySQL Foreign Key  - 22

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;

delete statement execution

Now let us perform the deletion operation as below: –

delete from teacher_d where t_id= 2;.

MySQL Foreign Key  - 24

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:

deletion statement

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.

  1. MySQL Self Join
  2. MySQL Constraints
  3. Unique Key in MySQL
  4. ANY in MySQL

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - ENROLL NOW