[MySQL] foreign_key_checks 알아보기

 

- 목차

 

들어가며.

이번 글에서는 MySQL 의 테이블 간 외래키 제약 (Foreign Key Constraints) 을 On/Off 할 수 있는 설정인 foreign_key_checks 에 대해서 알아보려고 합니다.

 

On and Off foreign_key_checks.

MySQL 실행하기.

먼저 실습을 위해서 도커를 활용한 MySQL 서버를 실행시켜보겠습니다.

docker run -d --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=1234 mysql:8.0.23

 

그리고 Foreign Key 관계의 테이블 2개를 생성해보겠습니다.

docker exec -it mysql mysql -uroot -p1234

create database test;
use test;
create table school (
    id int PRIMARY KEY,
    name varchar(64)
) engine = InnoDB;

create table student (
    id int PRIMARY KEY,
    name varchar(64),
    school int,
    FOREIGN KEY (school) REFERENCES school(id)
);

insert into school values (1, 'A'), (2, 'B'), (3, 'C');
insert into student values (1, 'Andy', 1), (2, 'Brad', 2), (3, 'Cindy', 3);

 

위 명령어들을 실행하게 되면,

school 와 student 데이터들이 3개씩 생성됩니다.

select school.id      as school_id,
       school.name    as school_name,
       student.id     as student_id,
       student.name   as student_name,
       student.school as student_school
from school
         inner join student on school.id = student.school

+---------+-----------+----------+------------+--------------+
|school_id|school_name|student_id|student_name|student_school|
+---------+-----------+----------+------------+--------------+
|1        |A          |1         |Andy        |1             |
|2        |B          |2         |Brad        |2             |
|3        |C          |3         |Cindy       |3             |
+---------+-----------+----------+------------+--------------+

 

 

foreign_key_checks 테스트하기.

만약 school 데이터를 삭제하려고 한다면 Foreign Key Constraints 에 위반되어 데이터를 삭제할 수 없습니다.

그래서 반드시 Foreign Key 관계가 있는 Student 데이터를 먼저 제거해야만 Student 데이터를 삭제할 수 있습니다.

아래 예시와 같이 school 의 레코드를 삭제하게 되면 Foreign Key 관계인 다른 student 의 Row 에 의해서 쉽게 삭제할 수 없습니다.

delete from school where id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`school`) REFERENCES `school` (`id`))

 

아래와 같이 foreign_key_checks 옵션을 비활성화하게 되면 외래키 제약없이 데이터를 삭제할 수 있습니다.

set foreign_key_checks = 0;
delete from school where id = 1;
set foreign_key_checks = 1;

 

그리고 school 데이터가 삭제된 이후에 데이터를 조회하게 되면 아래와 같이 외래키 제약이 무시된 결과를 확인할 수 있죠.

select student.id     as student_id,
       student.name   as student_name,
       student.school as student_school,
       school.id      as school_id,
       school.name    as school_name
from student
         left join school on school.id = student.school

+----------+------------+--------------+---------+-----------+
|student_id|student_name|student_school|school_id|school_name|
+----------+------------+--------------+---------+-----------+
|1         |Andy        |1             |null     |null       |
|2         |Brad        |2             |2        |B          |
|3         |Cindy       |3             |3        |C          |
+----------+------------+--------------+---------+-----------+