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.
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
id is the primary key in this table.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> CREATE TABLE student (id int(11) primary key auto_increment, name varchar(255), address text, phone varchar(50)) ENGINE=InnoDB; mysql> desc student; +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(255) | YES | | NULL | | | address | text | YES | | NULL | | | phone | varchar(50) | YES | | NULL | | +---------+--------------+------+-----+---------+----------------+ |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> CREATE TABLE student_courses (id int(11) primary key auto_increment, course varchar(255), student_id int(11), FOREIGN KEY (student_id) REFERENCES student(id)) ENGINE=InnoDB; mysql> desc student_courses; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | course | varchar(255) | YES | | NULL | | | student_id | int(11) | YES | MUL | NULL | | +------------+--------------+------+-----+---------+----------------+ |
Let’s insert some values into student table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> insert into student(name, address, phone) values('John','USA','999999999'); Query OK, 1 row affected (0.01 sec) mysql> insert into student(name, address, phone) values('Vikas','India','999999999'); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +----+-------+---------+-----------+ | id | name | address | phone | +----+-------+---------+-----------+ | 1 | John | USA | 999999999 | | 2 | Vikas | India | 999999999 | +----+-------+---------+-----------+ 2 rows in set (0.00 sec) |
Let’s insert some values into student_courses table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> insert into student_courses(course, student_id) values('My Sql',1); Query OK, 1 row affected (0.01 sec) mysql> insert into student_courses(course, student_id) values('PHP',2); Query OK, 1 row affected (0.01 sec) mysql> select * from student_courses; +----+--------+------------+ | id | course | student_id | +----+--------+------------+ | 1 | My Sql | 1 | | 2 | PHP | 2 | +----+--------+------------+ |
So we have populated dummy records. Let’s check what happens if we try to delete a record from student table.
|
1 2 3 4 5 |
mysql> delete from student where id = 2; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`sundaysim`.`student_courses`, CONSTRAINT `student_courses_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)) |
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.
|
1 2 3 4 5 |
insert into student_courses(course, student_id) values('PHP',3); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`sundaysim`.`student_courses`, CONSTRAINT `student_courses_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)) |
How to a create Foreign key constraint through Alter command
Take student and student_courses table.
|
1 |
ALTER TABLE student_course ADD FOREIGN KEY (student_id) REFERENCES student(id) |
