외래키로 참조 중인 컬럼 수정하기

1. 서론

아래와 같이 post, user 각각의 테이블이 존재합니다.

post 테이블은 user 테이블의 userNo 컬럼을 참조하고 있습니다. 

create table user
(
    userNo    bigint                              not null primary key,
    userId    varchar(50)                         not null,
    userName  varchar(50)                         not null,
    userPwd   varchar(100)                        null,
    constraint user_userId_uindex unique (userId)
);


create table post
(
    postNo     bigint unsigned auto_increment      primary key,
    userNo     bigint                              null,
    title      varchar(255)                        not null,
    author     varchar(255)                        not null,
    // user 테이블의 userNo를 외래키로 설정 했습니다.
    constraint post_ibfk_2 foreign key (userNo) references user (userNo)
);

 

 

2. 문제점 

기존의 bigint 자료형의 범위는 "-2147483648 ~2147483647" 입니다. 하지만 unsigned 선언하게 되면 "0 ~ 4294967295" 음수의 값이 양수로 모두 옮겨지게 됩니다. 

 

보통의 회원관리 데이터베이스에서는 음수가 굳이 필요하지 않습니다. 좀 더 큰 범위를 확보하고자 unsigned 선언을 하려고 합니다. 

 

아래와 같이 데이터 타입의 변경을 시도합니다. 

alter table user modify userNo bigint unsigned;
alter table post modify userNo bigint unsigned;

 

이때 [3780] 에러가 발생 합니다.

 

외래키는 기본적으로 하나의 컬럼이 다른 컬럼에 의존합니다. 그리고 두 테이블 간의 관계가 형성됩니다. 즉 외래키와 참조되는 원본 컬럼은 같은 조건을 가지고 있어야 합니다. 그리고 이때 하나의 컬럼만 변경할 경우 에러가 발생합니다. 참조 무결성이 지켜지지 않기 때문입니다. 

 

한마디로 외래키 참조를 하고 있는 경우 하나의 컬럼만 임의로 변경할 수 없습니다. 이것이 외래키의 장점이자 단점입니다. 참조 무결성을 지킬 수 있지만, 위와 같이 중간에 속성을 변경할 수 없습니다. 

 

어떻게 해야 할까요?

 

3. 해결방법

// Foreign key 제약조건 drop
alter table post drop foreign key 제약조건 이름;

// FK 제약조건이 없으니, 해당 테이블의 컬럼 속성 변경
alter table user modify userNo bigint unsigned;
alter table post modify userNo bigint unsigned;

// post 테이블의 userNo, user 테이블의 userNo 컬럼을 외래키 추가
alter table post add foreign key (userNo) references user (userNo);

1. 외래키 제약 조건 drop
2. 컬럼 속성 변경
3. 다시 외래키 제약 조건 추가

---

다른 해결 방법

set foreign_key_checks = 0; // FK 제약조건 off

alter table user modify userNo bigint unsigned;
alter table post modify userNo bigint unsigned;

set foreign_key_checks = 1; FK 제약조건 on

 

좀 더 간단한 해결 방법입니다.

 

이때 주의해야 할 점은 한 스크립트에서 실행을 해야 합니다. 각각의 스크립트는 각각의 connection pool을 가지기 때문입니다.