Let’s use a minimalistic example to illustrate the concept of a parent and child table. Consider the below two tables containing some information on pupils and and their respective teachers:

pupil table:

id	name
1	axel
2	bobby
3	cathy

Refer here for code to generate the pupil table.

teacher table:

teacher_id	teacher_name	pupil_id	department
1	emily	1	physics
2	fiona	2	physics
3	gilbert	3	chemistry

Refer here for code to generate the teacher table.

Checking the code for each table, you will see that the pupil_id field of the teacher table is referring to the id field of the pupil table.
Therefore in this example the pupil table is the parent table and the teacher table is the child table.

Importance of a Foreign Key

Whenever one table is referring to another table, it is almost always a good idea to use foreign keys. Let’s understand why using a simple example.

Let’s use the same tables again:

pupil (PARENT TABLE)

id	name
1	axel
2	bobby
3	cathy

teacher (CHILD TABLE)

teacher_id	teacher_name	pupil_id	department
1	emily	1	physics
2	fiona	2	physics
3	gilbert	3	chemistry

Suppose we add a new teacher who will be supervising a new student pupil_id=4:

teacher (CHILD TABLE)

teacher_id	teacher_name	pupil_id	department
1	emily	1	physics
2	fiona	2	physics
3	gilbert	3	chemistry
4	helen	4	physics

Can you see the inconsistency in our data at the current moment? We have a teacher who is supervising a pupil who does not exist in our pupil table (i.e. we have no student with id=4 in the pupil table).

NOTE

Foreign keys are constraints that prevent us from these inconsistencies in our data. In other words, with a foreign key in place, we will not be able to add the Helen record since MySQL knows that the pupil_id=4 does not have a corresponding match in the id field of the pupil table.

To prove this, let’s try to insert the teacher Helen into our teacher table, who will be supervising a new pupil with pupil_id = 4:


        
        
            
                
                
                    INSERT INTO teacher(teacher_name, pupil_id) 
VALUES ('helen', 4);
                
            
            ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails.

Great, so the foreign keys are useful in preventing inconsistency in our database!

Deleting from the Parent table

Another scenario where an inconsistency might occur is the deletion of a record in the parent table. For instance, suppose we try to delete student with id=2 from pupil table:


        
        
            
                
                
                    DELETE FROM pupil 
WHERE id = 2;

This will result in an error since the child table contains a record with pupil_id=2, which would no longer have a matching record in the parent table if bobby (i.e. id=2) was deleted.

pupil (PARENT TABLE)

id	name
1	axel
2	bobby
3	cathy

teacher (CHILD TABLE)

teacher_id	teacher_name	pupil_id	department
1	emily	1	physics
2	fiona	2	physics
3	gilbert	3	chemistry

Moreover, when we also update the parent table, the same inconsistency might occur:


        
        
            
                
                
                    UPDATE pupil 
SET id = 3 
WHERE id = 2;

All of these inconsistencies can be prevented when we use a foreign key. Therefore, the recommendation is to use it!

By default there are no constraints on the foreign key, and it can take NULL as well as duplicate values.

Temporarily disabling a foreign key constraint in MySQL

We can temporarily disable foreign key constraints on a session level in MySQL by using the following: SET FOREIGN_KEY_CHECKS = 0;

chevron_right

Searching all foreign keys to a table or column in MySQL

We can search for all foreign key constraints pointing to a particular table or a particular column in MySQL. The syntax varies slightly between searching for tables and searching for columns.

chevron_right

Adding foreign key to an existing table in MySQL

We can add a foreign key to an existing table in MySQL using the following general syntax: ALTER TABLE table_name ADD FOREIGN KEY (column_name) REFERENCES parent_table(parent_column);

chevron_right

Published by Arthur Yanagisawa

Edited by 0 others

Did you find this page useful?

thumb_up

thumb_down

Comment

Citation

Ask a question or leave a feedback...

thumb_up

thumb_down

chat_bubble_outline

settings

Enjoy our search

Hit / to insta-search docs and recipes!

MySQL | Foreign keys

Parent and Child table

Importance of a Foreign Key

Deleting from the Parent table

Related