테이블 수정과 삭제

-- Oracle: 단일 컬럼
ALTER TABLE employees ADD phone VARCHAR2(20);

-- Oracle: 여러 컬럼은 괄호로 묶음
ALTER TABLE employees ADD (
    phone VARCHAR2(20),
    address VARCHAR2(200) DEFAULT '미입력'
);

-- MySQL/PostgreSQL: 괄호 불필요, COLUMN 키워드는 선택 가능
ALTER TABLE employees ADD phone VARCHAR(20);
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);

-- SQL Server
ALTER TABLE employees ADD phone NVARCHAR(20);

-- 잘못된 방법 (기존 데이터 때문에 에러)
ALTER TABLE employees ADD grade VARCHAR(10) NOT NULL;
-- ERROR: 기존 행의 grade가 NULL이므로 제약 위반

-- 올바른 방법 1: DEFAULT 값 함께 지정
ALTER TABLE employees ADD grade VARCHAR(10) NOT NULL DEFAULT 'JUNIOR';

-- 올바른 방법 2: 단계적 추가
ALTER TABLE employees ADD grade VARCHAR(10);            -- NULL 허용으로 추가
UPDATE employees SET grade = 'JUNIOR' WHERE grade IS NULL; -- 값 채우기

-- Oracle / MySQL
ALTER TABLE employees MODIFY grade VARCHAR(10) NOT NULL;   -- NOT NULL 적용

-- PostgreSQL
ALTER TABLE employees ALTER COLUMN grade SET NOT NULL;

-- 첫 번째 컬럼으로 추가
ALTER TABLE employees ADD employee_code VARCHAR(20) FIRST;

-- 특정 컬럼 뒤에 추가
ALTER TABLE employees ADD phone VARCHAR(20) AFTER email;

-- Oracle: MODIFY
ALTER TABLE employees MODIFY (name VARCHAR2(200));
ALTER TABLE employees MODIFY (salary NUMBER(12,2) DEFAULT 0);

-- MySQL: MODIFY
ALTER TABLE employees MODIFY name VARCHAR(200);
ALTER TABLE employees MODIFY name VARCHAR(200) NOT NULL;

-- PostgreSQL: ALTER COLUMN
ALTER TABLE employees ALTER COLUMN name TYPE VARCHAR(200);
ALTER TABLE employees ALTER COLUMN salary SET DEFAULT 0;
ALTER TABLE employees ALTER COLUMN name SET NOT NULL;

-- SQL Server: ALTER COLUMN
ALTER TABLE employees ALTER COLUMN name NVARCHAR(200);

-- 크기 축소 전: 최대 길이 확인
SELECT MAX(LENGTH(name)) FROM employees;  -- 결과: 85
-- 85자 이하로는 줄일 수 없음

-- 타입 변경 전: 변환 가능 여부 확인 (MySQL)
SELECT age FROM employees WHERE age NOT REGEXP '^[0-9]+
#x27;;

-- PostgreSQL
SELECT age FROM employees WHERE age !~ '^[0-9]+
#x27;;

-- Oracle
SELECT age FROM employees WHERE NOT REGEXP_LIKE(age, '^[0-9]+
#x27;);
-- 숫자가 아닌 값이 있는지 확인

-- 대부분의 DBMS에서 지원
ALTER TABLE employees DROP COLUMN phone;

-- Oracle: 여러 컬럼 동시 삭제
ALTER TABLE employees DROP (phone, fax, pager);

-- Oracle: 큰 테이블에서 컬럼 비활성화 (빠름)
ALTER TABLE employees SET UNUSED COLUMN phone;
-- 나중에 실제 삭제
ALTER TABLE employees DROP UNUSED COLUMNS;

-- Oracle / PostgreSQL
ALTER TABLE employees RENAME COLUMN name TO full_name;

-- MySQL: CHANGE (타입도 함께 명시해야 함)
ALTER TABLE employees CHANGE name full_name VARCHAR(200);

-- MySQL 8.0+: RENAME COLUMN
ALTER TABLE employees RENAME COLUMN name TO full_name;

-- SQL Server: sp_rename 프로시저
EXEC sp_rename 'employees.name', 'full_name', 'COLUMN';

-- Oracle / PostgreSQL
ALTER TABLE employees RENAME TO staff;

-- MySQL
RENAME TABLE employees TO staff;

-- Oracle
RENAME employees TO staff;

-- SQL Server
EXEC sp_rename 'employees', 'staff';

-- PRIMARY KEY 추가
ALTER TABLE employees ADD CONSTRAINT pk_emp PRIMARY KEY (emp_id);

-- FOREIGN KEY 추가
ALTER TABLE orders ADD CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(user_id);

-- CHECK 제약 추가
ALTER TABLE employees ADD CONSTRAINT ck_age CHECK (age >= 0 AND age <= 150);

-- UNIQUE 제약 추가
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);

-- NOT NULL 추가 (Oracle)
ALTER TABLE employees MODIFY (name NOT NULL);

-- NOT NULL 추가 (PostgreSQL)
ALTER TABLE employees ALTER COLUMN name SET NOT NULL;

-- Oracle / PostgreSQL / SQL Server: 이름으로 삭제
ALTER TABLE employees DROP CONSTRAINT ck_age;

-- MySQL: 외래키는 DROP FOREIGN KEY
ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;

-- MySQL: UNIQUE 제약은 보통 인덱스 이름으로 삭제
ALTER TABLE users DROP INDEX uq_email;

-- MySQL / Oracle: PRIMARY KEY 삭제
ALTER TABLE employees DROP PRIMARY KEY;

-- NOT NULL 해제 (Oracle)
ALTER TABLE employees MODIFY (name NULL);

-- NOT NULL 해제 (PostgreSQL)
ALTER TABLE employees ALTER COLUMN name DROP NOT NULL;

-- 비활성화
ALTER TABLE employees DISABLE CONSTRAINT ck_age;

-- 활성화 (기존 데이터 검증 포함)
ALTER TABLE employees ENABLE CONSTRAINT ck_age;

-- 활성화 (기존 데이터 검증 건너뛰기 — 새 데이터만 검사)
ALTER TABLE employees ENABLE NOVALIDATE CONSTRAINT ck_age;

-- 기본 삭제
DROP TABLE employees;

-- 테이블이 존재할 때만 삭제 (에러 방지)
DROP TABLE IF EXISTS employees;  -- MySQL, PostgreSQL, SQL Server 2016+

-- Oracle은 버전에 따라 IF EXISTS 지원 여부가 다르므로
-- 미지원 환경에서는 PL/SQL 블록으로 처리
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE employees';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN  -- ORA-00942: table or view does not exist
            RAISE;
        END IF;
END;

-- 에러 발생
DROP TABLE users;
-- ERROR: Cannot drop table 'users' referenced by a foreign key constraint

-- Oracle: CASCADE CONSTRAINTS로 참조하는 FK도 함께 삭제
DROP TABLE users CASCADE CONSTRAINTS;

-- PostgreSQL: CASCADE로 참조하는 FK와 종속 객체 삭제
DROP TABLE users CASCADE;

-- MySQL: FK 검사 비활성화 후 삭제 (최후수단)
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE users;
SET FOREIGN_KEY_CHECKS = 1;

-- 삭제 (휴지통으로 이동)
DROP TABLE employees;

-- 휴지통 확인
SELECT object_name, original_name FROM recyclebin;

-- 복구
FLASHBACK TABLE employees TO BEFORE DROP;

-- 완전 삭제 (휴지통에 남기지 않음)
DROP TABLE employees PURGE;

-- 휴지통 비우기
PURGE RECYCLEBIN;

-- 기본
TRUNCATE TABLE access_logs;

-- Oracle: 스토리지 반환 옵션
TRUNCATE TABLE access_logs DROP STORAGE;    -- 할당된 공간 반환 (기본)
TRUNCATE TABLE access_logs REUSE STORAGE;   -- 할당된 공간 유지

🟢 낮은 위험 (대부분 안전):
  * NULL 허용 컬럼 추가 (기존 행에 영향 없음)
  * 상수 DEFAULT 컬럼 추가 (DBMS가 빠른 추가를 지원할 때)
  * 컬럼 이름 변경 (애플리케이션 코드 수정 필요)
  * 새로운 인덱스 생성 (Online DDL 사용 시)

🟡 중간 위험 (테스트 필수):
  * 컬럼 크기 변경 (확대는 안전, 축소는 위험)
  * NOT NULL 제약 추가 (기존 NULL 데이터 확인 필요)
  * 외래키 추가 (기존 데이터 정합성 확인 필요)
  * 인덱스 삭제 (의존하는 쿼리 확인 필요)

🔴 높은 위험 (점검 시간 고려):
  * 대용량 테이블 컬럼 타입 변경 (전체 행 재작성)
  * DEFAULT 없이 NOT NULL 컬럼 추가 (기존 행 위반)
  * PRIMARY KEY 변경 (테이블 재구성)
  * 테이블 파티셔닝 적용

1. 사전 조사
   □ 변경 대상 테이블의 데이터 건수 확인
   □ 관련 인덱스, 뷰, 트리거, 프로시저 목록 확인
   □ 외래키 참조 관계 확인
   □ 해당 테이블을 사용하는 애플리케이션 쿼리 목록

2. 개발 환경 테스트
   □ 동일 데이터 규모로 테스트 (10만 건 vs 1억 건은 다름)
   □ 변경 소요 시간 측정
   □ 락 발생 범위 확인
   □ 롤백 시나리오 테스트

3. 변경 SQL 준비
   □ 변경 SQL 작성 및 리뷰
   □ 롤백 SQL 작성 (복원용)
   □ 검증 SQL 작성 (변경 확인용)

4. 적용
   □ 서비스 영향 최소화 (점검 시간 활용 / 온라인 DDL)
   □ 변경 전 백업
   □ 변경 적용
   □ 검증 SQL 실행
   □ 애플리케이션 정상 동작 확인

5. 사후 관리
   □ 성능 모니터링 (새 인덱스, 변경된 쿼리 플랜)
   □ 에러 로그 확인
   □ 롤백 필요 시 준비된 SQL 실행

-- MySQL 5.6+ Online DDL
ALTER TABLE employees ADD phone VARCHAR(20), ALGORITHM=INPLACE, LOCK=NONE;

-- ALGORITHM 옵션:
--   INPLACE: 가능한 경우 내부/온라인 방식으로 처리
--            작업에 따라 재구성이 발생할 수 있음
--   COPY:    임시 테이블에 복사 후 교체 (느리지만 확실)
--   INSTANT: 지원 조건을 만족하면 메타데이터 중심으로 빠르게 처리

-- LOCK 옵션:
--   NONE:      DML 동시 실행을 목표로 함
--              짧은 메타데이터 락은 발생할 수 있음
--   SHARED:    읽기 허용, 쓰기 차단
--   EXCLUSIVE: 읽기/쓰기 모두 차단

-- 컬럼 추가가 거의 즉시 완료 (메타데이터만 변경)
ALTER TABLE employees ADD phone VARCHAR(20), ALGORITHM=INSTANT;
-- 지원 조건을 만족하면 대용량 테이블에서도 매우 빠르게 완료

-- 온라인 인덱스 생성
CREATE INDEX idx_emp_name ON employees(name) ONLINE;

-- 온라인 인덱스 재구성
ALTER INDEX idx_emp_name REBUILD ONLINE;

-- 온라인 테이블 재구성 (Oracle 12c+)
ALTER TABLE employees MOVE ONLINE;

-- PostgreSQL ALTER TABLE은 작업 종류에 따라 AccessExclusiveLock,
-- 전체 테이블 스캔, 테이블 재작성 여부가 달라짐
-- NOT NULL 추가 등은 전체 테이블 스캔이 필요할 수 있음

-- NOT NULL 추가 시 CHECK 제약으로 대체 (큰 테이블에서 유용)
ALTER TABLE employees ADD CONSTRAINT chk_phone_nn CHECK (phone IS NOT NULL)
    NOT VALID;  -- 기존 데이터 검사 건너뛰기
-- 나중에 백그라운드에서 검증
ALTER TABLE employees VALIDATE CONSTRAINT chk_phone_nn;

-- 실제 NOT NULL 속성이 필요하면 검증 후 별도로 전환
ALTER TABLE employees ALTER COLUMN phone SET NOT NULL;

1. 새로운 구조의 빈 테이블 _employees_new 생성
2. 기존 테이블에 트리거 설치 (INSERT/UPDATE/DELETE 동기화)
3. 기존 데이터를 배치로 복사 (청크 단위)
4. 복사 완료 후 테이블 이름 교체 (RENAME TABLE)
5. 기존 테이블과 트리거 삭제

장점:
  * 복사 중에도 대부분 DML 가능 (짧은 잠금/부하 발생 가능)
  * 최종 교체 락 시간이 짧은 편 (RENAME은 메타데이터 변경 중심)

단점:
  * 트리거 오버헤드 (복사 중 DML 성능 저하)
  * 디스크 공간이 2배 필요 (원본 + 복사본)
  * FK가 있는 테이블에서는 사용이 복잡

-- 요구사항: 회원 등급 기능 추가
ALTER TABLE users ADD grade VARCHAR(10) DEFAULT 'BRONZE';
CREATE INDEX idx_users_grade ON users(grade);

-- 기존 VIP 고객 등급 업데이트 (매출 기준)
UPDATE users SET grade = 'GOLD'
WHERE user_id IN (
    SELECT user_id FROM orders
    GROUP BY user_id
    HAVING SUM(total_amount) >= 1000000
);

-- 요구사항: 사용자당 여러 연락처 저장
-- 기존 phone 컬럼은 1개만 저장 가능

-- 1. 연락처 테이블 생성
CREATE TABLE user_contacts (
    contact_id  INT PRIMARY KEY AUTO_INCREMENT,
    user_id     INT NOT NULL REFERENCES users(user_id),
    contact_type VARCHAR(10) NOT NULL,  -- 'PHONE', 'FAX', 'MOBILE'
    contact_value VARCHAR(20) NOT NULL,
    is_primary  BOOLEAN DEFAULT FALSE
);

-- 2. 기존 데이터 이전
INSERT INTO user_contacts (user_id, contact_type, contact_value, is_primary)
SELECT user_id, 'PHONE', phone, TRUE
FROM users
WHERE phone IS NOT NULL;

-- 3. 기존 컬럼 삭제 (데이터 이전 확인 후)
ALTER TABLE users DROP COLUMN phone;

-- 요구사항: 회원 탈퇴 시 데이터 보존 (법적 의무)
ALTER TABLE users ADD is_deleted BOOLEAN DEFAULT FALSE;
ALTER TABLE users ADD deleted_at TIMESTAMP NULL;

-- 기존 DELETE 로직을 UPDATE로 변경해야 함
-- 기존: DELETE FROM users WHERE user_id = ?;
-- 변경: UPDATE users SET is_deleted = TRUE, deleted_at = NOW() WHERE user_id = ?;