MySQL 의 foreign key 와 Index 의 관계

해당 페이지에서는 MySQL 을 사용 할 시, foreign key (FK) 와 index 의 관계에 대해서 다룬다.

 

이 페이지는 아래의 MySQL 공식 문서 및 외부 blog 들을 참조한다.

 

[1] https://dev.mysql.com/doc/refman/8.0/en/constraint-foreign-key.html#:~:text=MySQL%20requires%20that%20foreign%20key,column%2C%20an%20index%20is%20created.&text=InnoDB%20and%20NDB%20tables%20support%20foreign%20keys

 

[2] https://ivvve.github.io/2020/07/08/server/rdb/is-fk-indexed/

 

[3] https://co1nam.tistory.com/44

 

[4] https://brunch.co.kr/@dan-kim/26

 

또한, 이 글은 아래의 이전 게시글과 연관되어 있다.

 

[5] 2021.03.09 - [DB 관련 지식/DB 기본5개념] - 인덱스 (Index)

 

 

FK 의 개념과 의의

 

Foreign Key (외래키) 는 일반적으로 여러 개의 테이블들이 나누어서 구성되어 있는 DB 에서 각 테이블별로 구성되는 정보를 참조를 하면서 각 데이터들의 유효성을 보장하도록 도입 된 개념이다. 이에 대한 예시는 [2],[3],[4] 에 자세하게 안내가 되어 있기에 이를 참조 하면 좋다.

 

 

MySQL 에서 FK 와 index 의 관계

 

FK 는 개념적으로만 생각 해 보면 유효성을 보장한다는 점에서 좋은 점만 있는 것으로 보일 수 있다.

그러나 실제 업무에서 사용하다 보면 각 DB 별 특성으로 인하여 어려움을 겪을 수 있다.

이는 MySQL 에서 대표적으로 겪을 수 있다.

 

[1] 을 확인 해 보면, MySQL 은 FK 와 index 는 필수불가결의 관계다. 그래서 FK 를 생성할 때, index 를 MySQL 이 임의로 생성하게 된다.

 

여기에 DB 업무를 담당하는 실무자들이 index 가 임의로 생성된 column 에 다시 index 를 생성하려고 하면 에러가 발생하게 된다.

 

또한, [3] 을 확인해보면, 유효성을 보장하기 위해 설정된 FK 가 결국 이후에 데이터 또는 테이블 구조 변경 시 하나의 장애물로 작용하게 된다. 

 

 

MySQL 에서 FK 관련 error 발생 시, 확인 방법

 

이에 따라 위와 관련된 작업을 수행 시, error 가 발생한다면 이를 확인 할 수 있는 가장 쉬운 방법은 SHOW ENGINE INNODB STATUS 를 사용하는 것이다.

 

FK 에러가 발생하면 아래와 같은 예시로 기록이 남는다.

 

------------------------
LATEST FOREIGN KEY ERROR
------------------------
130811 23:36:38 Error in foreign key constraint of table test/t2:
FOREIGN KEY (t1_id) REFERENCES t1 (id)):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.