개요
A테이블의 id컬럼을 참조하는 B테이블이 있다.
여기서 A테이블의 id컬럼 값을 변경하려고 할 때,
데이터 일관성을 유지해야하기 때문에 참조 무결성의 제약 조건으로 업데이트를 할 수 없다.
하지만 가끔 잘못 넣은 값을 다른 값으로 업데이트해주어야 할 때가 있는데,
FK 때문에 참조 관계로 엮여있어서 계속 오류가 발생하여 값을 업데이트하지 못한다.
발생되는 오류는 다음과 같다.
Error Code: 1451.
Cannot delete or update a parent row:
a foreign key constraint fails
(`database`.`FK table`, CONSTRAINT `FK Name` FOREIGN KEY (`id`)
REFERENCES `table` (`id`))
ON UPDATE CASCADE를 사용하는 방법이 있지만...
테이블을 변경했다가 다시 되돌리는 건 사실 너무 귀찮고,
일회성으로 잠깐 빠르게 수정하고 싶다.
외래 키 제약 조건 활성화 / 비활성화 하기
특정 컬럼의 값이 외래 키 제약 조건에 걸려 값을 업데이트하지 못할 때
외래 키 제약 조건을 잠깐 비활성화 했다가 값을 업데이트 후,
다시 외래 키 제약 조건을 활성화 하면 된다.
- 외래 키 제약 조건 비활성화 쿼리
SET FOREIGN_KEY_CHECKS = 0;
- 외래 키 제약 조건 활성화 쿼리
SET FOREIGN_KEY_CHECKS = 1;
이 기능의 장점으로는 필요한 작업을 신속하게 수행할 수 있다는 것인데,
외래 키 제약 조건을 비활성화하면 데이터 무결성이 손상될 수 있으므로
상황에 따라 반드시 필요한 경우에만 비활성화하고 작업이 완료되면 즉시 다시 활성화하는 것이 좋다.
또한, 다중 사용자 환경에서 이 설정을 변경하면 다른 사용자가 수행하는 작업에도 영향을 미칠 수 있으므로 주의하여야 한다.
외래 키 제약 조건이 걸린 행 업데이트 예시
예시를 위한 샘플 테이블
설명을 위해 간소화 하였다.
product 테이블
- product_id, int, PK
- name, varchar(255)
product_image 테이블
- image_id, int, PK
- url, varchar(255)
- product_id, int, FK ( product(product_id) )
예시를 위한 샘플 데이터
product 테이블
| product_id | name | |
| 1행 | 999 | 아이템1 |
product_image 테이블
| image_id | url | product_id | |
| 1행 | 123 | https:~~ | 999 |
시나리오
외래 키로 참조하는 product_id의 값이 999이지만, 1로 변경하고 싶다.
하지만 외래 키 제약 조건으로 인해서 업데이트가 불가능한 상황이다.
작성 쿼리
-- 외래 키 제약 조건 비활성화
SET FOREIGN_KEY_CHECKS = 0;
-- product 테이블 강제 업데이트
update product
set product_id = 1;
-- product_image 테이블 강제 업데이트
update product_image
set product_id = 1;
-- 외래 키 제약 조건 활성화
SET FOREIGN_KEY_CHECKS = 1;
변경 전 / 후 값 비교
- 변경 전: product_id 999
- 변경 후: product_id 1
