개요
요구하는 서비스에 따라 가끔은 데이터베이스의 테이블을 수정해야하는 경우가 있다.
특히 MS-SQL(SSMS)를 사용하면서 컬럼명을 추가/변경/삭제를 하게 되면 자주 볼 수 있는 오류인데,
테이블의 구조를 변경할 수 없다는 내용이다.
이번 글에서는 테이블 구조 변경 시 발생하는 오류가 왜 발생하는지의 원인과
간단한 해결 방법을 다뤄보려 한다.
Error
테이블의 구조를 변경하고 저장하려니 다음과 같은 오류가 발생한다.
Saving changes is not permitted.
The changes that you have made require the following tables to be dropped and re-created.
You have either made changes to a table that can't be re-created
or enabled the option Prevent saving changes that require the table to be re-created.
원인
이 오류 메시지는 SQL Server Management Studio (SSMS)에서
테이블의 스키마(열 정의, 데이터 형식 등)에 변경했을 때 발생하는 에러이다.
SSMS에서는 특정 변경 사항(예: 열의 추가, 데이터 형식 변경 등)이 발생하면,
테이블을 삭제하고 새로 생성하여 변경 사항을 적용하게 되는데,
SSMS의 기본 설정으로는 이러한 동작이 차단되도록 옵션이 설정되어 있기 때문에
테이블을 삭제 및 재생성해야 하는 변경을 허용하지 않아 발생하는 오류이다.
해결 방법
이 문제를 해결하려면, SSMS 설정에서 해당 옵션을 비활성화해야 한다.
SSMS에서 아래의 메뉴를 누른다.
- Tools - Options
- (한글판의 경우) 도구 - 옵션
옵션에서 아래의 해당 옵션을 체크 해제하고, 적용을 위해 OK를 눌러 옵션 적용을 마친다.
- Designers - Table and Database Designers
- 'Prevent saving changes that require table re-creation' 체크 해제
- (한글판의 경우) 디자이너 - 테이블 및 데이터베이스 디자이너
- '테이블을 다시 만들어야 하는 변경 내용 저장 사용 안함' 체크 해제
테스트
테이블의 컬럼을 추가 한 뒤, 테이블을 저장해보자.
아래와 같은 확인 문구가 출력된다.
"테이블이 데이터베이스에 저장됩니다. 계속하시겠습니까?"
이제 'Yes'를 누르면 구조가 변경된 테이블을 저장할 수 있게 된다.
테이블 구조 변경 시 테이블이 삭제 후 재생성 되는 이유는?
MS-SQL에서 테이블 구조를 변경할 때 테이블이 삭제되고 새로 생성되는 이유는
일부 스키마 변경 사항이 테이블의 물리적 데이터 저장 구조에 영향을 미치기 때문이다.
이 경우, 테이블의 기존 구조를 그대로 유지하면서 스키마를 수정하는 것이 불가능하기 때문인데,
SQL Server는 테이블을 삭제하고 재생성하는 방식으로 변경 사항을 적용한다.
이 방법은 변경된 스키마가 데이터베이스 엔진에 올바르게 반영되도록 보장한다.
조금 더 풀어쓰면,
테이블의 스키마가 변경되면 SQL Server는 모든 데이터 페이지를 다시 작성해야 할 수도 있기 때문이다.
새로 추가된 열의 공간을 확보하거나 삭제된 열의 데이터를 제거하는 작업이 필요한데
이 경우, 단순히 테이블의 메타데이터만 수정하는 것이 아니라 물리적인 데이터 저장 방식을 변경해야 할 수 있다.
또한, 열의 데이터 형식을 변경하는 경우에도 기존 데이터의 형식을 새로운 형식에 맞게 변환하는 과정에서도
테이블의 모든 행을 검사하고 데이터 형식을 변환한 후, 새로운 형식으로 다시 저장해야 하기 때문에
기존 테이블을 삭제하고 새로 생성하는 과정을 필요로 한다.