Introduction to MySQL Foreign Key

Let us see an example for the same.

Here is what we defined and could 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 acts as “Foreign Key”.
A table can have more than one foreign key, which are 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 take place when values in parent key are deleted or updated.

MySQL has five reference options:

Cascade: Cascade is nothing but whatever action that has been taken in the parent table it 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 with 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 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 to insert the row data of ‘loan_no’ which is no present 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 update or delete the row. Hence it pops up an error.
As “No action” and “Restrict” 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” that we have created with the below statement.

DROP TABLE BORROWER_DETAILS;

Now let us create the table “BORROWER_DETAILS” WITH 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: –

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 perform the update or delete of the row. It updates in the child table as well.

Now let us select the child table “borrower_details” to check if the updates has 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” that we have created with below statement.

DROP TABLE BORROWER_DETAILS;

Now let us create the table “BORROWER_DETAILS” WITH 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.

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 value of the foreign key to NULL on performing deletion or updation of the matched column in 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 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. Like if we perform update on the parent table then the child table will set the value to “NULL”. If we perform Delete operation on the parent table, then same will be performed in the child table as well.

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 perform update on the parent table then 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 set 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 then the child table will be delete as well, as we have mention 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 delete 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 performed output:

We could see in the output that the row of T_ID=2 has been deleted from the parent and child table as well.

SELECT * FROM TEACHER_D; SELECT * FROM TEACHER_SUBJECT_DETAILS;

Screenshot is for the same:

Conclusion

Things that’s need to be taken care: –

Foreign Key is a combination of a single column or group of columns in a table that links to the single or group of columns in another table. The foreign key provides constraints on data in related tables, which allows to main referential Integrity. A table can have more than one foreign key, which are the references to the primary key of different tables.

Quiz Result
Total Questions	Correct Answers	Wrong Answers	Percentage

Introduction to MySQL Foreign Key

Syntax and Parameters

How does MySQL Foreign Key work?

1. With “No Action” and “Restrict”

2. CASCADE

3. SET NULL Action

Examples to Implement MySQL Foreign Key

Conclusion

Recommended Articles

About Us

Apps

Resources

Certification Courses