MariaDB 강좌 / ALTER TABLE로 구조 변경하기: 열·제약 조건·변경 전 점검

ALTER TABLE은 기존 테이블의 열·타입·제약 조건 등 구조를 바꾸는 명령입니다. 기존 데이터가 새 규칙을 만족하는지 먼저 검사하고, 새 열 추가·데이터 채우기·제약 적용을 단계적으로 진행해야 실패나 정보 손실을 줄일 수 있습니다.

구조 변경 전에 현재 정의와 복구 계획 남기기

운영 구조 변경은 이름만 바꾸는 편집이 아닙니다. 열 타입 축소·NOT NULL·UNIQUE를 적용하면 기존 행 때문에 실패하거나 값이 변환될 수 있습니다. SHOW CREATE TABLE과 데이터 백업, 사용하는 프로그램의 열 의존 관계를 확인하고 별도 환경에서 먼저 재현합니다. 추가할 조건의 뜻은 NULL·기본값·NOT NULL·CHECK로 입력 조건 정하기에서 먼저 정하고, 손실 가능성이 있는 구조 변경의 복구 자료는 mariadb-dump로 데이터베이스 백업하기처럼 별도로 준비합니다.

기존 상품 행이 있는 연습 테이블 준비하기

학습용 MariaDB 서버의 shopcourse에 course_user로 접속합니다. 명령줄 클라이언트와 UTF-8 SQL 파일 실행 방법대로 코드를 UTF-8 파일에 저장하고 SOURCE C:/mariadb-lab/lesson.sql;처럼 실제 파일 경로로 실행할 수 있습니다. SQL 문은 운영체제 터미널이 아닌 MariaDB 연결 안에서 실행합니다.

먼저 SELECT VERSION(), DATABASE();와 SHOW TABLES;로 대상 서버·DB·이름 중복을 확인합니다. 아래 products_38_lab는 이 글의 독립된 연습용 이름입니다. 이미 존재하면 정의와 행을 확인하고, 보관할 자료가 없는 이 글의 실습 테이블임이 확실할 때만 마지막 절의 정리 순서로 지운 뒤 다시 만듭니다. 기존 강좌의 동명 없는 테이블이나 운영 데이터는 삭제하지 않습니다.

USE shopcourse;
CREATE TABLE products_38_lab (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  note VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT ck_p38_price CHECK (price >= 0),
  CONSTRAINT ck_p38_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_38_lab (product_id,product_name,category,price,stock,note) VALUES (1, '노트', '문구', 3500.00, 20, NULL),
  (2, '볼펜', '문구', 1500.00, 0, ''),
  (3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
  (4, '스탠드', '생활', 25000.00, 5, NULL),
  (5, '파일', '문구', 1500.00, 50, ''),
  (6, '케이블', '전자', 8000.00, 0, '호환 확인');

모든 상품에 새 코드 sku를 추가한다는 상황을 가정합니다. 기존 데이터에 값을 먼저 채울 수 있도록 처음에는 NULL을 허용합니다. 이 코드는 실습용 규칙이며 실제 코드 체계의 길이·불변성·고유 범위는 업무에서 정해야 합니다.

새 열 추가와 기존 데이터 채우기를 분리하기

SHOW CREATE TABLE products_38_lab;
ALTER TABLE products_38_lab ADD COLUMN sku VARCHAR(20) NULL;
UPDATE products_38_lab SET sku=CONCAT('P',LPAD(product_id,3,'0'))
WHERE sku IS NULL;
SELECT COUNT(*) AS rows_total,COUNT(sku) AS known_sku,
 COUNT(DISTINCT sku) AS unique_sku FROM products_38_lab;

새 열은 여섯 기존 행에서 처음 NULL입니다. UPDATE가 P001~P006을 채운 뒤 rows_total·known_sku·unique_sku는 모두 6이어야 합니다. 값이 비어 있거나 중복되었다면 다음 제약 적용으로 넘어가지 말고 먼저 입력 정책과 데이터를 바로잡습니다.

ALTER와 UPDATE를 하나의 ROLLBACK 가능한 묶음으로 착각하지 않습니다. 구조 작업은 암묵적 확정 등 별도 규칙을 가지므로 각 단계의 성공 상태를 기록하고, 중간에 중단되었을 때 이어 할지 복원할지 결정할 수 있어야 합니다.

NOT NULL·UNIQUE·CHECK 적용 후 실제 실패 검사하기

ALTER TABLE products_38_lab
 MODIFY COLUMN sku VARCHAR(20) NOT NULL,
 ADD CONSTRAINT uq_p38_sku UNIQUE(sku),
 ADD CONSTRAINT ck_p38_name CHECK(CHAR_LENGTH(product_name)>0);
SHOW CREATE TABLE products_38_lab;
SELECT product_id,product_name,sku FROM products_38_lab ORDER BY product_id;

sku는 NULL을 허용하지 않고 중복도 막으며 상품명 길이는 0보다 커야 합니다. MODIFY에는 보존할 타입·길이·NULL·기본값·속성을 실제 정의와 비교해 명시합니다. 일부 속성을 생략해도 언제나 그대로 남는다고 가정하지 않습니다. ALTER TABLE의 열·제약 변경 문법을 확인하세요.

별도 문장으로 상품 2의 sku를 P001로 바꾸면 UNIQUE 오류가 예상됩니다. 실패 후 기본키·원래 sku를 조회해 P002가 유지되는지 확인합니다. 빈 상품명 입력도 CHECK가 적용되는지 확인하되, 검사 설정을 끄는 방식으로 통과시키지 않습니다.

UPDATE products_38_lab SET sku='P001' WHERE product_id=2;

잠금·온라인 변경·시간 비용을 판단하기

같은 ALTER라도 버전·엔진·변경 종류·테이블 크기에 따라 내부 복사나 잠금이 필요할 수 있습니다. ALGORITHM·LOCK 옵션을 임의로 지정했다고 모든 변경이 무중단으로 가능한 것은 아닙니다. 실제 버전에서 지원하는 조합과 실패 동작, 애플리케이션의 잠금 대기까지 확인합니다.

긴 트랜잭션이 메타데이터 잠금을 잡고 있으면 ALTER가 대기할 수 있습니다. 작은 연습 테이블에서 빨리 끝난 시간으로 운영 소요 시간을 예측하지 말고 데이터량·인덱스·디스크·복구 시간을 포함해 검토합니다. 필요하면 유지보수 창과 점진적 호환 배포를 계획합니다.

원상복구의 의미와 정리 확인하기

새 열을 DROP하는 것은 그 열에 쌓인 데이터를 없애는 것이므로 원래 데이터 복구와 같지 않습니다. 손실이 가능한 변경은 이전 백업과 복원 절차가 필요합니다. 이 실습에서는 원본 여섯 상품의 이름·가격·재고가 보존되었는지 확인한 후 전용 테이블을 정리합니다.

반복하기 전 현재 DB와 실제 정의·행을 확인하고 필요한 자료를 백업합니다. 보관할 데이터가 없는 이 글의 전용 실습 객체만 아래 순서로 정리합니다. 외래키가 있으면 자식부터 지웁니다. DROP TABLE은 구조와 데이터를 모두 없애며 일반적인 ROLLBACK으로 복원되지 않으므로 취소 가능한 DML 실습과 분리합니다.

DROP TABLE shopcourse.products_38_lab;