MySQL 8.0.22 | On Update Cascade & Delete Cascade + Set Null

Hi there, what a nice day to learn more about MySQL!

Like I told you on my last posting, we’re going to make tables with Foreign Key that you can revise or delete freely today.

1. On Update Cascade & Delete Cascade

It’s very easy to use these constraints. You can simply put on delete cascade and on update cascade right next to the foreign key in the child table. If you don’t know why do we have to use them, it’d be better to checkout my last posting.

MySQL 8.0.22 | Rules for the tables with Foreign Key

***** 1. Parent Table *****
create table member(
id varchar(3) primary key,
name varchar(5)
);
***** 2. Child Table *****
create table car(
id varchar(3),
carNum varchar(4),
foreign key(id) references member(id)
on delete cascade
on update cascade
);

Now let’s test it if we used it right.

insert into member values
('AAA', 'Nina'),
('BBB', 'Tom'),
('CCC','Amy');
insert into car values
('AAA', '1234'),
('AAA', '4567'),
('BBB', '7777'),
('CCC','0000');

Put these tuples in each table and see what happens if I change the id ‘AAA’ into ‘DDD’ in the parent table.

mysql> update member set id='DDD' where id='AAA';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from member;
+-----+------+
| id  | name |
+-----+------+
| BBB | Tom  |
| CCC | Amy  |
| DDD | Nina |
+-----+------+
3 rows in set (0.00 sec)
mysql> select * from car;
+------+--------+
| id   | carNum |
+------+--------+
| DDD  | 1234   |
| DDD  | 4567   |
| BBB  | 7777   |
| CCC  | 0000   |
+------+--------+
4 rows in set (0.00 sec)

Without any errors, we could change the information from the parent table, and when we changed it, this affected on the child table as well.
Now I’ll delete the information of id ‘BBB’ in the parent table.

mysql> delete from member where id='BBB';
Query OK, 1 row affected (0.01 sec)
mysql> select * from member;
+-----+------+
| id  | name |
+-----+------+
| CCC | Amy  |
| DDD | Nina |
+-----+------+
2 rows in set (0.00 sec)
mysql> select * from car;
+------+--------+
| id   | carNum |
+------+--------+
| DDD  | 1234   |
| DDD  | 4567   |
| CCC  | 0000   |
+------+--------+
3 rows in set (0.00 sec)

That was easy!

2. On Delete Set Null & Update Set Null

And there is another constraint that we can use on foreign key, on delete set null . The way to use it is just same as the cascade.
We use it when we want to delete the tuples from the parent table while not deleting the information from child table.
And the thing is, you can’t add cascade or set null constraint after you created the tables. So I’ll drop the ones above and create the new ones!

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)
on delete set null
);

And now I’ll put the same tuples from above, then I’ll delete the person who’s id is ‘AAA’.

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

You see there’s no id ‘AAA’ in the parent table anymore, but her id became ‘Null’ in the child table. You can put on update set null as well if you want, it will work very similarly with on delete set null .

And that was the pretty much everything of basic foreign key. Well done guys!
On the next post, I’ll be talking about ‘Join’ clause. See ya!