MySQL 에서 외래 키 제약조건(Foreign Key Constraint) 끄고 작업하기

Owned by 정광섭
Last updated: 2021-Feb-08
2 min read


외래 키 제약조건은 데이타의 무결성을 유지하고 JOIN 성능을 높이는 좋은 방법이지만 외래 키 컬럼의 데이타 유형이 바뀌는 경우 변경 사항 적용이 어려워집니다


예로 다음과 같이 작가와 post 테이블이 참조 관계로 있다고 가정해 보겠습니다.

CREATE TABLE writers (
    id int NOT NULL auto_increment,
    PRIMARY KEY (id)
);

CREATE TABLE posts (
    id int not null auto_increment,
    writer_id int,
    primary key (id),
    INDEX  writer_idx (writer_id),
    FOREIGN KEY (writer_id)
        REFERENCES writers(id)
) ;


서비스가 활성화되고 작가가 늘어나서  id 가 int 범위로는 감당이 안 되어서 다음과 같이  BIG INT 타입으로 변경하려고 하면

alter table writers modify id bigint unsigned;


참조하는 컬럼 유형과 다르다는 MySQL 3730 에러가 발생합니다.

[HY000][3730] Cannot drop table 'writer' referenced by a foreign key constraint 'post_ibfk_1' on table 'post'.


그래서 사전에 참조 테이블을 바꾸려고 다음 DDL 을 실행하면 

alter table posts modify writer_id bigint unsigned;


이번에는 원본 테이블의 컬럼 유형과 다르다는 MySQL 3780 에러를 만나게 됩니다.

[3780] Referencing column 'writer_id' and referenced column 'id' in foreign key constraint 'posts_ibfk_1' are incompatible.


이런 문제를 해결하기는 매우 번거로우며 가장 간단한 방법으로는 DDL(Data Definition Language) 구문을 수행하기 전에 외래 키 제약 조건을 끄고 DDL 이 끝나면 외래 키 제약 조건을 켜는 방법이 있습니다.


MySQL 에서는 SET FOREIGN_KEY_CHECKS = 0; 명령으로 외래 키 제약 조건을 끄고 SET FOREIGN_KEY_CHECKS = 1; 로 활성화할 수 있습니다.

SET FOREIGN_KEY_CHECKS=0;

-- DDL 수행

SET FOREIGN_KEY_CHECKS=1;


위의 예에서 writers 테이블의 id 를 BIG INT 로 바꾸고 posts 테이블의 writer_id 도 BIG INT 로 바꾸려면 아래처럼 실행하면 됩니다.

SET FOREIGN_KEY_CHECKS = 0;
ALTER table writers modify id bigint unsigned;
ALTER table posts modify writer_id bigint unsigned;
SET FOREIGN_KEY_CHECKS = 1;



같이 보기


Ref