Foreign Key in Mysql : How to Create a Foreign Key in Mysql

How to create a Foreign key in MySql. What’s the use of Foreign key constraint in a MySql. In this tutorial, You’ll learn about Foreign key constraint and it’s advantages.

What is Foreign Key in MySql

 

In simple words, A Foreign key is a reference to a primary key in another table.

Advantage of Foreign Key

i) Foreign key helps in maintaining referential integrity. It means if a value is in the one table then it must also exist in the other table. Any attempt to break this constraint will give an error.

ii) It helps in understanding the relationship between tables just by looking at the table definitions.

iii) When you create a Foreign key it automatically creates an index on that column.

NOTE : Foreign is only supported by InnoDB storage engine. So if you are using MyISAM then you can’t use Foreign key constraint in your tables.

MyISAM vs InnoDB.

Difference between inner join and left join.

How to Create a Foreign Key in MySql

To understand much deeper about Foreign Key. Let’s create two tables student and student_courses.

student table contains following columns-
i) id
ii) name
iii) address
iV) phone

Subscribe Our Tutorials

Get Latest Updates on Facebook

id is the primary key in this table.

student_courses table contains following columns-
i) id
ii) course
iii) student_id

id is the primary key and student_id is the Foreign key which points to the id column of student table.

Let’s insert some values into student table.

Let’s insert some values into student_courses table.

So we have populated dummy records. Let’s check what happens if we try to delete a record from student table.

NOTE : This error occurs because data in the student_courses table depends on the data in the student table. The Foreign key constraint prevents you from deleting student record, without first deleting the student_courses.

Let’s insert a record with student_id 3. We have only two students in student table with id 1 and 2. So if we try to insert any value in student_courses with student_id 3, we get an error. Let’s check it.

How to a create Foreign key constraint through Alter command

Take student and student_courses table.

MySql interview questions

MySql Foreign key documentation

WebRewrite

About WebRewrite

I am technology lover who loves to keep updated with latest technology. My interest field is Web Development.
Bookmark the permalink.