MYSQL, FOREIGN_KEY_CHECKS 사용 방법, 주의사항

MYSQL 이용 시 테이블을 수정하거나 삭제해야 하는데, 외래 키 제약(foreign key constraint) 때문에 번거롭게 부모-자식 테이블 관계를 확인해가며 쿼리문을 수행한 적이 있으신가요? 이럴 때 유용하게 사용할 수 있는 게 FOREIGN_KEY_CHECKS입니다.

 

[외래키 제약 위반 시 오류 발생]

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

FOREIGN_KEY_CHECKS

FOREIGN_KEY_CHECKS는 외래 키를 참조하는 테이블에서 UPDATE, DELETE 등 쿼리문을 수행할 때 외래 키 제약 조건을 확인할지 여부를 결정하는 옵션입니다. 기본 값은 1로 제약조건을 확인하고, 0으로 바꾸면 제약조건에 관계 없이 쿼리문을 수행합니다.

FOREIGN_KEY_CHECKS 사용 방법

아래와 같이 사용합니다.

-- foreign key 제약 체크(기본값)
SET FOREIGN_KEY_CHECKS = 1;

-- foreign key 제약 미체크
SET FOREIGN_KEY_CHECKS = 0;

예시로 설명드리겠습니다.

SET FOREIGN_KEY_CHECKS = 1;

-- 부모 테이블(전자제품) 생성
  CREATE TABLE electronic_device
  (
     company VARCHAR(90)PRIMARY KEY,
     name VARCHAR(90)
  );

-- 자식 테이블(스마트폰) 생성 후 기업명(company)에 외래키 적용
  CREATE TABLE smartphone
  (
     name VARCHAR(90),
     company VARCHAR(90),
     FOREIGN KEY (company) REFERENCES electronic_device(company) 
  );

-- 자식 테이블에 값 추가 시도. electronic_device에는 samsung이 존재하지 않다고 가정.
INSERT INTO smartphone VALUES ('galaxy', 'samsung'); 

-- 외래키 제약 위반 에러 발생
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

smartphone이라는 테이블에는 반드시 electronic_device에 존재하는 company만 삽입될 수 있는데, samsung이 존재하지 않아 INSERT에 실패하는 모습입니다.

 

이제 FOREIGN_KEY_CHECKS을 0으로 바꾸고 수행해보겠습니다.

SET FOREIGN_KEY_CHECKS = 0;

-- 자식 테이블에 값 추가 시도. electronic_device에는 samsung이 존재하지 않다고 가정.
INSERT INTO smartphone VALUES ('galaxy', 'samsung'); 

-- 그럼에도 INSERT 성공
-- Query OK, 1 row affected (0.03 sec)

동일한 조건이지만 INSERT에 성공합니다.

FOREIGN_KEY_CHECKS 사용 시 주의사항

이처럼 FOREIGN_KEY_CHECKS를 0으로 두면 설정한 외래키 제약에 관계없이 쿼리문을 수행할 수 있습니다. 그러나 안 되는 걸 되게 한 것이기 때문에 외래키를 위반한 테이블이 형성될 수 있습니다. 왜냐하면 쿼리문 수행 후 FOREIGN_KEY_CHECKS을 다시 1로 돌려놓는다고 해서 mysql이 테이블에 위반 사항이 있는지 확인해주지 않습니다.

 

때문에 FOREIGN_KEY_CHECKS를 0으로 놓고 쿼리문을 수행하는 방법은 부모 자식 테이블에 삽입된 값을 초기화하고 다시 넣을 때나 테이블을 삭제하고 다시 만들 때 등 제약사항을 위반하지 않지만 쿼리문 수행 순서를 결정하기 번거로울 때 활용하는 게 좋을 거 같습니다.