[Database / mysql] Understanding ‘on delete cascade’

By | January 27, 2017

As I was studying the database, I came up with the syntax ‘on delete cascade’.
When a tuple is deleted, the tuple associated with the foreign key is deleted as well.

But there was a part that was not clearly understood.

 

If we delete the tuple of relation A, does tuple of relation B is erased consecutively?
Or if we delete the tuple of relation B, does tuple of relation A be erased consecutively?

 

I have checked the mysql directly for ambiguity.

I have described all the trial and error that I have experienced while checking.

 

1. Create a TEST database for verification.

1
2
3
4
5
mysql> create database TEST;
Query OK, 1 row affected (0.00 sec)
mysql> use TEST;
Database changed

 

2. Create a table that points to its own primary key with a foreign key.

-> A ‘personinfo’ table that contains ‘age’ and ‘parent’ information. And because the person and the parent must exist unconditionally, not null.

1
2
3
4
5
6
7
8
mysql> create table personinfo (
    -> person char(10) not null,
    -> age int(11),
    -> parent char(10) not null,
    -> primary key(person),
    -> foreign key(parent) references personinfo(person)
    -> on delete cascade);
Query OK, 0 rows affected (0.07 sec)

 

3. I tried ‘insert’ for confirmation but an error occurred.

-> Considering that person and parent must be entered as not null, parent must point to person. In an empty situation, there is no point to input, which is not possible at all.

1
2
3
4
5
mysql> insert into personinfo values (
    -> "peopleA", 30, "peopleB" );
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`TEST`.`personinfo`, CONSTRAINT `personinfo_ibfk_1` FOREIGN KEY (`parent`)
 REFERENCES `personinfo` (`person`) ON DELETE CASCADE)

 

4. on delete cascade Change the table structure for confirmation.

-> Change the structure to a child that can not be existed.

1
2
3
4
5
6
7
8
mysql> create table personinfo2 (
    -> person char(10) not null,
    -> age int(11),
    -> child char(10),
    -> primary key(person),
    -> foreign key(child) references personinfo2(person)
    -> on delete cascade);
Query OK, 0 rows affected (0.07 sec)

 

5. Enter the person information.

(“PeopleA”, 30, NULL)

(“PeopleB”, 60, “peopleA”)

(“PeopleC”, 25, NULL)

1
2
3
4
5
6
7
8
9
10
mysql> insert into personinfo2 values ( "peopleA", 30, NULL );
Query OK, 1 row affected (0.03 sec)
mysql> insert into personinfo2 values (
    -> "peopleB", 60, "peopleA" );
Query OK, 1 row affected (0.02 sec)
mysql> insert into personinfo2 values (
    -> "peopleC", 25, NULL );
Query OK, 1 row affected (0.03 sec)

 

6. Confirm your input information.

1
2
3
4
5
6
7
8
9
mysql> select * from personinfo2;
+---------+------+---------+
| person  | age  | child   |
+---------+------+---------+
| peopleA |   30 | NULL    |
| peopleB |   60 | peopleA |
| peopleC |   25 | NULL    |
+---------+------+---------+
3 rows in set (0.00 sec)

 

7. Confirm results

7-1. When deleting the information of “peopleB”, confirm that the “peopleA” information indicated by the foreign key is also deleted

-> The “peopleA” information that “peopleB” points to as a foreign key is not deleted.

1
2
3
4
5
6
7
8
9
10
11
mysql> delete from personinfo2 where person = "peopleB";
Query OK, 1 row affected (0.04 sec)
mysql> select * from personinfo2;
+---------+------+-------+
| person  | age  | child |
+---------+------+-------+
| peopleA |   30 | NULL  |
| peopleC |   25 | NULL  |
+---------+------+-------+
2 rows in set (0.00 sec)

 

7-2. When deleting the information of “peopleA”, confirm that the information indicating “peopleA” as foreign key is deleted together

-> Delete the “peopleB” information pointing to “peopleA” as a foreign key.

1
2
3
4
5
6
7
8
9
10
mysql> delete from personinfo2 where person = "peopleA";
Query OK, 1 row affected (0.02 sec)
mysql> select * from personinfo2;
+---------+------+-------+
| person  | age  | child |
+---------+------+-------+
| peopleC |   25 | NULL  |
+---------+------+-------+
1 row in set (0.00 sec)

 

conclusion

“On delete cascade” means that when B tuple points to A tuple with foreign key, if A tuple is deleted, B tuple is also deleted.

In conclusion, if you do not use “on delete cascade”, even if you delete the A tuple, the content (A tuple) remains in the foreign key of the B tuple and the integrity is broken. Considering this, it is obvious that if a tuple pointed to by a foreign key is deleted, it is obvious that there must be some action for the tuples that have that foreign key.

 

Leave a Reply

Your email address will not be published. Required fields are marked *