[Database/mysql] on delete cascade 에 대한 이해

Database를 공부하는 도중 on delete cascade 라는 문법을 만나게 되었다. 

찾아보니 설명상으로는 어떤 tuple이 삭제될 때 foreign key로 연결된 tuple 또한 같이 삭제된다는 내용이었다. 

의미를 보면 그런가보다 하고 넘어갈 수 있는 내용이었지만 명확히 이해가 가지 않는 부분이 있었다. 

위와 같을 경우 relation A의 tuple을 지우면 relation B의 tuple도 연달아 지워진다는 것인가? 아니면 relation B의 tuple을 지우면 relation A의 tuple들이 지워진다는 것인가?

애매한 부분이 있어서 직접 mysql 상에서 확인해 보았다.

확인하면서 겪은 시행착오까지 모두 기술하였다. 

1. 확인을 위한 TEST database 생성.

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

2. foreign key로 자기 자신의 primary key를 가리키는 table 생성.

--> 사람(person)을 기준으로 나이(age)와 부모(parent) 정보를 담을 수 있는 table. 자기 자신(person)과 부모(parent)는 무조건 존재해야 함으로 not null.

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. 확인을 위해 insert를 시도하였지만 에러 발생.

--> 생각해 보니 person과 parent는 not null로 무조건 입력을 해야 하는데, parent는 person을 가리켜야만 한다. empty 상황에서는 가리킬게 없어서 아예 입력이 불가능하다.

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 확인을 위한 table 구조를 바꿈.

--> 무조건 존재해야 하는 부모(parent) 정보 말고, 존재하지 않을 수도 있는 자식(child)으로 구조를 바꿈.

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. 사람 정보 입력.

("peopleA", 30, NULL)

("peopleB", 60, "peopleA")

("peopleC", 25, NULL)

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. 입력 정보 확인.

mysql> select * from personinfo2; +---------+------+---------+ | person | age | child | +---------+------+---------+ | peopleA | 30 | NULL | | peopleB | 60 | peopleA | | peopleC | 25 | NULL | +---------+------+---------+ 3 rows in set (0.00 sec)

7. 결과 확인

7-1. "peopleB"의 정보 삭제시 foreign key로 가리키고 있는 "peopleA" 정보도 같이 삭제되는지 확인

--> 삭제결과 "peopleB"가 foreign key로 가리키고 있는 "peopleA" 정보는 삭제되지 않는다. 

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. "peopleA"의 정보 삭제시 "peopleA"를 foreign key로 가리키는 정보들이 같이 삭제되는지 확인

--> 삭제결과  "peopleA"를 foreign key로 가리키고 있는 "peopleB" 정보도 같이 삭제된다. 

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)

결론

"on delete cascade"는 B tuple이 foreign key로 A tuple을 가리키고 있을 때, A tuple을 삭제하면 B tuple도 같이 삭제되는 기능이다. 

생각해보니, "on delete cascade"를 사용하지 않게되면 A tuple을 삭제하더라도 B tuple의 foreign key에 내용(A tuple)은 남아있게 되어 무결성이 깨진다. 이를 고려해 생각해 보면 foreign key로 가리키고 있는 tuple이 삭제되면 해당 foreign key를 가지고 있는 tuple들에 대해 어떤 조치가 있어야 하는 것이 당연한 것 같다.