외래 키 제약 걸린 컬럼, 안전하게 변경하는 SQL 가이드

글쓴이 /

데이터베이스 테이블에서 외래 키(Foreign Key)가 걸린 컬럼을 변경하려고 하면, “외래 키 제약 조건 때문에 변경할 수 없다”는 오류를 만날 수 있습니다. 이 글에서는 MySQL과 Oracle 환경에서 외래 키 제약이 설정된 컬럼을 안전하게 변경하는 방법과, 실무에서 유용하게 쓸 수 있는 팁을 함께 소개합니다.

MySQL에서 외래 키 제약 걸린 컬럼 변경하기

MySQL에서는 외래 키가 설정된 컬럼을 직접 수정하려고 하면 오류가 발생합니다. 이 경우, 먼저 기존 외래 키 제약을 제거한 뒤 컬럼을 수정하고, 이후 다시 외래 키를 생성하는 순서로 작업을 진행해야 합니다.

예시 테이블과 컬럼 정보

  • DB 테이블들
    • store_basic_info : 매장(가게) 기본 정보 테이블
      • store_code : 매장(가게) 중복 없는 고유 코드.
    • store_owner_info : 매장(가게) 사장님 정보 테이블
      • store_code : 매장(가게) 중복 없는 고유 코드.
  • store_basic_info의 store_code 기준으로 store_owner_info의 store_code가 외래 키로 걸려있는 상태.
  • store_basic_info의 store_code 컬럼이 숫자형 타입인데, 문자열 타입으로 변경 시도 시 오류가 난 상태.

전체 외래 키를 먼저 확인

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME IS NOT NULL;
SQL
  • fk_owner_store : 하나의 외래 키 목록 확인
  • 외래 키 테이블은 store_owner_info

외래 키 삭제하기

ALTER TABLE store_owner_info DROP FOREIGN KEY fk_owner_store;
SQL
  • 삭제 후 외래 키 목록을 다시 확인.
  • 삭제가 되었으면, 컬럼 정보를 변경

외래 키 재생성하기

ALTER TABLE store_owner_info
ADD CONSTRAINT fk_owner_store FOREIGN KEY (store_id)
REFERENCES store_basic_info(store_id)
ON DELETE CASCADE;
SQL
  • 첫 번째는 store_owner_info 테이블의 정보를 변경하겠다는 뜻.
  • 두 번째는 store_id 컬럼에 fk_owner_store의 이름으로 외래 키를 추가 한다는 뜻.
  • 세 번째는 외래 키가 참도할 대상은 store_basic_info 테이블의 store_id 컬럼으로 하겠는다는 뜻
  • 네 번째는 store_basic_info 테이블의 특정 store_id 값을 삭제할 때 store_owner_info에 동일한 store_id 값도 삭제된다는 뜻

⚠️ 주의: FK를 제거했다가 재생성하기 때문에, 중간에 참조 무결성에 주의해야 합니다.

Oracle에서 외래 키 제약 걸린 컬럼 변경하기

Oracle에서도 외래 키 제약 조건이 걸린 컬럼은 직접 수정할 수 없습니다.

컬럼 변경 순서

  1. 외래 키 제약 이름 조회
  2. 제약 비활성화 (DISABLE CONSTRAINT)
  3. 컬럼 변경
  4. 제약 재활성화 (ENABLE CONSTRAINT)

컬럼 변경을 위한 예제 쿼리

-- 1) 외래 키 제약 이름 조회
SELECT constraint_name
FROM user_constraints
WHERE table_name = 'STORE_MARKETING_INFO'
AND constraint_type = 'R';

-- 2) 제약 비활성화
ALTER TABLE store_marketing_info
DISABLE CONSTRAINT fk_marketing_store;

-- 3) 컬럼 변경 (예: VARCHAR2(20) → VARCHAR2(50))
ALTER TABLE store_basic_info
MODIFY store_id VARCHAR2(50);

-- 4) 제약 재활성화
ALTER TABLE store_marketing_info
ENABLE CONSTRAINT fk_marketing_store;
SQL
  • Oracle은 DISABLE CONSTRAINT를 사용하면 외래 키를 완전히 삭제하지 않고 유지할 수 있어 관리가 편합니다.
  • 제약을 재활성화할 때 기존 데이터가 제약 조건을 위반하면 활성화가 실패하니, 데이터 정합성 확인이 필수입니다.

💡 실무 활용 Tip

  • 컬럼 변경 전 백업 필수: 외래 키 관련 변경은 참조 테이블 데이터에 직접적인 영향을 줄 수 있으니, 변경 전 전체 백업을 권장합니다.
  • 외래 키 명명 규칙 통일: 제약 이름이 직관적이면 나중에 유지보수가 쉬워집니다. (예: fk_참조테이블_컬럼명)
  • 장기간 비활성화 주의: 제약 조건을 오랫동안 비활성화하면 데이터 무결성 문제가 발생할 수 있습니다.
  • 운영 DB라면 백업 필수.

마무리

외래 키 제약이 걸린 컬럼 변경은 단순 ALTER TABLE 명령으로는 불가능합니다. MySQL에서는 DROP → ALTER → ADD 방식, Oracle에서는 DISABLE → ALTER → ENABLE 방식이 안전합니다. 이 절차를 숙지하면 실무에서 컬럼 구조 변경 작업을 더 효율적이고 안정적으로 진행할 수 있습니다.

관련 포스팅들

위로 스크롤