EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Foreign Key 
Secondary Sidebar
MySQL Tutorial
  • Functions
    • MySQL Function
    • MySQL Aggregate Function
    • MySQL String functions
    • MySQL Date Functions
    • MySQL Window Functions
    • MySQL Math Functions
    • MySQL Boolean
    • Cursor in MySQL
    • Condition in MySQL
    • MySQL BETWEEN
    • Insert in MySQL
    • MySQL IFNULL()
    • MySQL TIMESTAMPDIFF()
    • MySQL COALESCE()
    • MySQL count()
    • MIN() in MySQL
    • MySQL Numeric
    • MySQL field()
    • MySQL FIND_IN_SET()
    • MySQL avg()
    • MySQL MAX() Function
    • MySQL BIN()
    • MySQL Concat
    • MySQL DECODE()
    • MySQL REGEXP_REPLACE()
    • MySQL Asynchronous
    • MySQL innodb_buffer_pool_size
    • MySQL key_buffer_size
    • MySQL TRUNCATE()
    • MySQL ROW_NUMBER()
    • NOT in MySQL
    • MySQL IN Operator
    • LIKE in MySQL
    • ANY in MySQL
    • MySQL NOT IN
    • MySQL CHECK Constraint
    • MySQL DISTINCT
    • MySQL ALL
    • MySQL Union
    • MySQL UNION ALL
    • MySQL EXISTS
    • MySQL ON DELETE CASCADE
    • MySQL REGEXP
    • MySQL Index
    • MySQL Add Index
    • MySQL REINDEX
    • MySQL UNIQUE INDEX
    • MySQL Clustered Index
    • MySQL? InnoDB Cluster
    • Table in MySQL
    • ALTER TABLE MySQL
    • MySQL Temporary Table
    • MySQL Clone Table
    • MySQL Repair Table
    • MySQL Lock Table
    • MySQL Optimize Table
    • TRUNCATE TABLE MySQL
    • MySQL?Table?Size
    • MySQL Table Dump
    • MySQL Update Set
    • MySQL ALTER TABLE Add Column
    • MySQL RANK()
    • MySQL CTE
    • MySQL LAG()
    • MySQL GROUP_CONCAT()
    • MySQL EXTRACT()
    • MySQL REPLACE
    • MySQL AUTO_INCREMENT
    • MySQL SYSDATE()
    • MySQL NULLIF()
    • MySQL Substring
    • MySQL SUBSTRING_INDEX()
    • MySQL LOWERCASE
    • MySQL Row
    • MySQL NOW
    • MySQL CEIL
    • MySQL Alias
    • MySQL Trigger
    • MySQL SHOW Triggers
    • MySQL UPDATE Trigger
    • MySQL DELETE Trigger
    • MySQL AFTER UPDATE Trigger
    • MySQL Stored Procedure
    • ROLLUP in MySQL
    • MySQL? INSTR()
    • MySQL Subquery
    • MySQL Timestamp
    • MySQL? Hour()
    • MySQL MOD()
    • MySQL DATE_FORMAT()
    • ALTER Column in MySQL
    • MySQL Rename Column
    • MySQL Interval
    • MySQL CURDATE
    • MySQL BIT
    • MySQL Binlog
    • MySQL Average
    • MySQL TEXT
    • MySQL SHOW
    • MySQL Offset
    • MySQL Timezone
    • mysql_real_escape_string
    • MySQL Datetime
    • MySQL DATE_SUB()
    • MySQL FULLTEXT
    • MySQL DATE_ADD()
    • MySQL sum()
    • MySQL Merge
    • MySQL BigInt
    • MySQL ROUND
    • MySQL VARCHAR
    • MySQL Decimal
    • MySQL Limit
    • MySQL today()
    • MySQL WEEKDAY
    • MySQL Split
    • MySQL Create Function
    • MySQL BLOB
    • MySQL encode()
    • MySQL Primary Key
    • MySQL Foreign Key
    • Unique Key in MySQL
    • MySQL Drop Foreign Key
    • MySQL DROP TRIGGER
    • MYSQL Database
    • Delete Database MySQL
    • MySQL Root
    • MySQL Root Password
    • MySQL Client
    • MySQL Users
    • MySQL?User Permissions
    • MySQL add user
    • MySQL List User
    • MySQL Show Users
    • MySQL User Password
    • MySQL?Cardinality
    • MySQL Workbench
    • MySQL Backup
    • MySQL REVOKE
    • MySQL Dump
    • MySQL Cluster
    • MySQL Full Text Search
    • MySQL Admin Tool
    • MySQL Export Database
    • MySQL Export to CSV
  • Basic
    • MySQL floor
    • MySQL DESCRIBE table
    • MySQL encryption
    • Introduction to MySQL
    • Is SQL Server a Database?
    • What is MySQL
    • Is MySQL Programming Language
    • MySQL Server
    • MySQL AB
    • MySQL Community Server
    • How To Install MySQL
    • MySQL Versions
    • MySQL OpenSource
    • MySQL GUI Tools
    • MySQL Grant
    • MySQL Error 1064
    • MySQL Drop Database
    • MySQL not equal
    • MySQL SELECT INTO Variable
    • MySQL Commands
    • MySQL Operators
    • What is MySQL Schema
    • Wildcards in MySQL
    • MySQL Constraints
    • MySQL Administration
    • MySQL Data Type
    • MYSQL COMMIT
    • MySQL FORMAT
    • Timestamp to Date in MySQL
    • MySQL DATEDIFF
    • MySQL?Incremental Backup
    • MySQL JSON Data Type
    • MySQL ENUM
    • MySQL Default Port
    • Cheat Sheet MySQL
  • Queries
    • MySQL Queries
    • MySQL Query Commands
    • SELECT in MySQL
    • MySQL INSERT IGNORE
    • MySQL having
    • ORDER BY in MySQL
    • MySQL Cheat Sheet
    • MySQL ORDER BY Random
    • MySQL ORDER BY DESC
    • MySQL GROUP BY
    • MySQL GROUP BY Count
    • MySQL GROUP BY month
    • MySQL WHERE Clause
    • MySQL WITH
    • MySQL FETCH
    • MySQL DDL
    • MySQL DML
    • MySQL WHERE IN Array
    • MySQL Fetch Array
    • MySQL ISNULL
    • MySQL Index Types
    • Mysql? Export Schema
    • Amazon RDS for MySQL
    • MySQL greatest
  • Database
    • What is Data Modeling
    • What is Data Processing
    • DBMS Architecture
    • DBMS Keys
    • Careers in Database Administration
    • What is MySQL Database
    • MySQL Relational Database
    • How to Connect Database to MySQL
    • MySQL Database Repair
    • RDBMS Interview Questions
    • DBMS Interview Questions
  • Joins
    • Joins in MySQL
    • MySQL Outer Join
    • Left Outer Join in MySQL
    • MySQL Self Join
    • Natural Join in MySQL
    • MySQL DELETE JOIN
    • MySQL Update Join
    • MySQL Cross Join
  • Advanced
    • MySQL Formatter
    • MySQL TINYINT
    • MySQL Grant All Privileges
    • MySQL DROP TABLE
    • MySQL rename database
    • MySQL Flush Privileges
    • MySQL super Privilege
    • MySQL Character Set
    • MySQL Log File
    • MySQL Flush Log
    • Grant Privileges MySQL
    • MySQL WHILE LOOP
    • IF Statement in MySQL
    • MySQL CASE Statement
    • MySQL IF Function
    • MySQL IF EXISTS
    • MySQL UUID
    • Views in MySQL
    • MySQL Replication
    • MySQL Partition
    • Toad for MySQL
    • Navicat for MySQL
    • MySQL AES_Encrypt
    • MySQL Performance Tuning
    • MySQL Transaction
    • MySQL? sort_buffer_size
    • MySQL? Sync
    • MySQL? Query Cache
    • MySQL Collation
    • MySQL ODBC Driver
    • MySQL Partitioning
    • MySQL InnoDB
    • MySQL Float vs Decimal
    • MySQL Union vs Union All
  • Interview Questions
    • MySQL Interview Questions

MySQL Foreign Key 

By Roja MetlaRoja Metla

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 the 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 for the same.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

loan

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

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

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 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.

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:

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 perform the update or delete of 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 has 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” 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;

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

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

MySQL Foreign Key  - 19

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;

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

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

deletion statement

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.

Recommended Articles

This is a guide to MySQL Foreign Key. Here we discuss an introduction to MySQL Foreign Key with syntax, working and examples for understanding better. You can also go through our other related articles to learn more –

  1. MySQL Self Join
  2. MySQL Constraints
  3. Unique Key in MySQL
  4. ANY in MySQL
Popular Course in this category
MySQL Training Program (12 Courses, 10 Projects)
  12 Online Courses |  10 Hands-on Projects |  92+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

MS SQL Training (16 Courses, 11+ Projects)4.9
Oracle Training (17 Courses, 8+ Projects)4.8
PL SQL Training (4 Courses, 2+ Projects)4.7
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - 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

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*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?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more