MySQL 8.0.22 | Rules for the tables with Foreign Key

Hey guys! This is Student Kim again. Welcome to another MySQL sesh with me.
Continue from the last time, I’ll talk about the Foreign Key today as well.
If you missed the last posting, here you go!

MySQL | How to create Foreign Key

And If you’re ready, let’s quickly create new tables with the Foreign Key.

create table member(
id varchar(3) primary key,
name varchar(5)
);
create table car(
id varchar(3),
carNum varchar(4),
foreign key(id) references member(id)
);

Here, As you see, there’s a table named ‘car’ which references the table ‘member’. So, member is a parent table, and the car is a child table.

mysql> select * from member;
+-----+------+
| id  | name |
+-----+------+
| AAA | Nina |
| BBB | Tom  |
+-----+------+
2 rows in set (0.00 sec)
mysql> select * from car;
+------+--------+
| id   | carNum |
+------+--------+
| AAA  | 1234   |
| AAA  | 4567   |
| BBB  | 7777   |
+------+--------+
3 rows in set (0.01 sec)

And I put these Tuples in them. So we can do some little experiments!

1. Possible errors for Child table

(1) Insert

Let’s say there’s a Amy who’s ID is ‘CCC’, and her carNum is ‘0000’. What would be happening if I try to put her in the ‘car’ table?

mysql> insert into car values
    -> ('CCC','0000');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`DB`.`car`, CONSTRAINT `car_ibfk_1` FOREIGN KEY (`id`) REFERENCES `member` (`id`))

There will be an error like this, because There’s no ID ‘CCC’ in the parent table ‘member’. You can’t reference the column that doesn’t exist.

(2) Update

How about updating tuples in the child table? It might seem to be ok, but it doesn’t. I’ll show you why.

mysql> update car set id='CCC' where id='BBB';
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`blog`.`car`, CONSTRAINT `car_ibfk_1` FOREIGN KEY (`id`) REFERENCES `member` (`id`))

Just like the insert, there are possibilities of causing error, from changing the value of the column which is referenced from the parent!

(3) Delete

Next, I’ll delete the carNum ‘4567’.

mysql> delete from car where carNum='4567';
Query OK, 1 row affected (0.01 sec)
mysql> select * from car;
+------+--------+
| id   | carNum |
+------+--------+
| AAA  | 1234   |
| BBB  | 7777   |
+------+--------+
2 rows in set (0.00 sec)

There’s no problems at all! You can delete any tuples in the child table.

2. Possible errors for Parent table

(1) Insert

What if I put id ‘CCC’ Amy in the parent table?

mysql> insert into member values
    -> ('CCC','Amy');
Query OK, 1 row affected (0.02 sec)
mysql> select * from member;
+-----+------+
| id  | name |
+-----+------+
| AAA | Nina |
| BBB | Tom  |
| CCC | Amy  |
+-----+------+
3 rows in set (0.00 sec)

It goes very well as you see.

(2) Update

What about updating? I’ll change the id ‘AAA’ into ‘CCC’.

mysql> update member set id='CCC' where id='AAA';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`blog`.`car`, CONSTRAINT `car_ibfk_1` FOREIGN KEY (`id`) REFERENCES `member` (`id`))

Of course there is an error. Because If I change values of the column which is referenced by child table, then the child table will lose its reference.

(3) Delete

For the last time, I’ll delete the person who has id ‘AAA’.

mysql> delete from member where id='AAA';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`blog`.`car`, CONSTRAINT `car_ibfk_1` FOREIGN KEY (`id`) REFERENCES `member` (`id`))

Because of the same reason of updating parent table, Deleting tuples in it also has possibility that can cause an error.

Today was more like theoretical session unlike the others on my blog. I hope you fully understand the rules above, but if you don’t, it’s also fine. Just understand there are some possibilities of errors of revising tables with the foreign key.
Because in the next session, I’ll teach you guys how to create the FK tables that you can revise freely, with using ‘delete/update on cascade/set null’. It’s gonna be fun!

Well done today guys! Thanks!