블로그를 이전 중이라 완료되기 전까지는 벨로그에 작성할 계획입니다.
이후 모든 글은 https://weekwith.me 에 작성 예정이니 다른 글이 궁금하시다면 해당 링크를 통해 방문해주세요.
도입
오늘은 LeetCode SQL 중 Medium 난이도의 문제인 1715. Count Apples and Oranges을 풀었다.
우선 아래와 같이 Boxes 및 Chests 테이블이 존재한다.
/*
Table: Boxes
Primary Key: box_id
Foreign Key: chest_id
*/
+--------------+------+
| Column Name | Type |
+--------------+------+
| box_id | int |
| chest_id | int |
| apple_count | int |
| orange_count | int |
+--------------+------+
/*
Table: Chests
Primary Key: chest_id
*/
+--------------+------+
| Column Name | Type |
+--------------+------+
| chest_id | int |
| apple_count | int |
| orange_count | int |
+--------------+------+테이블의 설명을 보면 알 수 있듯 Boxes 테이블 내에 존재하는 chest_id 필드는 외래 키(FK_Foreign Key)로 Chests 테이블의 chest_id 필드와 연결되어 있다. 문제에 예시로 제시된 레코드를 보면 box_id 필드의 값이 2 인 행의 chest_id 필드 값이 null 인 것을 확인할 수 있다.
-- Boxes
+--------+----------+-------------+--------------+
| box_id | chest_id | apple_count | orange_count |
+--------+----------+-------------+--------------+
| 2 | null | 6 | 15 |
| 18 | 14 | 4 | 15 |
| 19 | 3 | 8 | 4 |
| 12 | 2 | 19 | 20 |
| 20 | 6 | 12 | 9 |
| 8 | 6 | 9 | 9 |
| 3 | 14 | 16 | 7 |
+--------+----------+-------------+--------------+
-- Chests
+----------+-------------+--------------+
| chest_id | apple_count | orange_count |
+----------+-------------+--------------+
| 6 | 5 | 6 |
| 14 | 20 | 10 |
| 2 | 8 | 8 |
| 3 | 19 | 4 |
| 16 | 19 | 19 |
+----------+-------------+--------------+LeetCode에서 여러 SQL 문제를 풀 때 고민하는 부분 중 하나는 외래 키라 명시가 안 되어 있는데 다른 테이블의 필드를 참조하는 형태로 테이블이 구성되어 있을 때 과연 원래의 값이 삭제되면 이를 참조하고 있던 테이블의 동기화 여부다.
이러한 고민을 확장하여 그렇다면 과연 명시적으로 외래 키라고 작성되어 있는 경우 원래의 데이터와 이를 참조하는 데이터 간의 관계를 정의할 수 있는 옵션이 과연 무엇이 있는지 한번 살펴보고자 한다.
외래 키 옵션
MySQL에는 참조 행동(Referential Action)으로 총 다섯 가지가 있다.
하나씩 살펴보기 전에 먼저 외래 키를 정의하는 쿼리를 먼저 보고자 한다. 아래와 같이 테이블을 생성할 때 FOREIGN KEY 구를 통해 외래 키를 설정해줄 수 있으며 REFERENCES 부분을 통해 어떤 테이블의 필드를 참조하는지, 그리고 ON 부분을 통해 참조 행동을 결정할 수 있다. 위 예시로 제시되었던 Boxes 테이블을 생성할 때 chest_id 필드를 외래 키로 선정한 방법을 임의적으로 구현해본 것이다.
CREATE TABLE Boxes (
box_id INT NOT NULL,
chest_id INT,
PRIMARY KEY (box_id)
FOREIGN KEY (chest_id)
REFERENCES Chests (chest_id)
ON UPDATE CASCADE DELETE SET NULL
)이때 예시 레코드에서 Boxes 테이블에 chest_id 필드의 값이 null 인 경우가 존재했던 이유는 위 ON 부분에 DELETE SET NULL 이라는 옵션이 정의되었기 때문이다. UPDATE 부분은 CASCADE 옵션이 설정되어 있는데 SET NULL 옵션과 CASCADE 옵션에 대한 설명은 아래에서 할 것이기 때문에 UPDATE 및 DELETE 연산에 개별적으로 참조 행동을 설정할 수 있다는 것만 기억하자.
RESTRICT
우선 RESTRICT 옵션이다. 단어 그대로 부모 테이블에 대한 UPDATE 및 DELETE 연산을 거절한다는 의미다.
다시 말해 부모 테이블인 Chests 테이블을 참조하는 자식 테이블 Boxes 테이블이 존재하기 때문에 이러한 참조 과정에서의 오류를 막기 위해 자식 테이블이 참조하고 있는 부모 테이블의 필드에 대한 UPDATE 밒 DELETE 연산을 못하게 막는 것이다.
만약 FOREIGN KEY 를 생성할 때 별다른 참조 행동을 설정하지 않으면 기본적으로 UPDATE 및 DELETE 연산 모두 RESTRICT 옵션이 정의된다.
NO ACTION
표준 SQL에 등장하는 키워드다. MySQL에서는 앞서 살펴본 RESTRICT 옵션과 동일하다.
예를 들어 SQL Server와 같이 특정 데이터베이스는 연산의 대상이 되는 외래 키의 값이 자식 테이블에서 참조되고 있는지 확인한 뒤에 이 옵션이 적용되도록 미뤄지지만 MySQL은 연산과 즉시 옵션이 먼저 실행되기 때문에 결과적으로 RESTRICT 옵션과 동일한 기능을 한다. 다시 말해 테이블의 참조 여부를 확인하지 않고 즉시 거절하는 것이다.
CASCADE
부모 테이블에서 UPDATE 및 DELETE 연산이 발생하면 이를 참조하고 있는 자식 테이블에도 자동으로 연산이 행해지는 옵션이다. 따라서 만약 부모 테이블에서 특정 행을 지우면 그 행에 있는 어떤 필드를 외래 키로 참조하고 있던 자식 테이블의 행들은 전부 함께 삭제된다.
한 가지 유의할 점은 두 테이블이 서로 참조하고 있는 관계에 있을 때 CASCADE 옵션이 성공하기 위해서는 양 테이블 모두 부모 테이블이면서 동시에 자식 테이블이어야 한다는 것이다. 만약 그렇지 않고 한쪽 테이블이 부모 테이블이면서 한쪽 테이블은 자식 테이블이기만 하면 CASCADE 옵션은 연산에 실패하여 오류를 반환한다.
SET NULL
자식 테이블의 필드가 참조하는 부모 테이블의 필드에 UPDATE 및 DELETE 연산이 발생하면 해당 자식 테이블의 필드의 값이 null 이 된다. 위 Boxes 테이블의 경우 SET NULL 옵션에 의해 box_id 필드의 값이 2 인 행의 chest_id 외래 키 값이 null 이 된 것이다.
한 가지 유의할 점은 자식 테이블에서 외래 키 필드의 옵션을 NOT NULL 로 설정하면 안 된다는 것이다. 그러면 해당 필드에 null 값이 올 수 없기 때문에 SET NULL 옵션은 연산에 실패한다.
SET DEFAULT
MySQL 파서(Parser)에 인식되는 옵션인데, MySQL의 스토리지 엔진(Storage Engine)인 InnoDB 및 NDB는 이 옵션을 UPDATE 및 DELETE 부분에 모두 사용하지 못하게 막는다.
스토리지 엔진이란
CREATE,SELECT등의 Create Read Update Delete (CRUD) 연산을 수행하기 위해 사용하는 컴포넌트(Componenet)를 의미한다.기본적으로, 그리고 가장 보편적으로 InnoDB를 사용하며 그 밖에 MyISAM, NDB 등이 있다. 각 스토리지 엔진 비교나 클러스터(Cluster) 등에 대한 부분들은 추후에 더 자세하게 공부하고 작성하고자 한다.
쉽게 별도의 설정을 하지 않으면 InnoDB 스토리지 엔진을 사용하고, InnoDB 스토리지 엔진은
SET DEFAULT옵션을 사용하지 못하게 막는다는 것만 생각하면 된다.
참고
더 자세한 내용은 MySQL 공식문서 중 13.1.20.5 외래 키 제약을 살펴보면 된다.
