2과목 · SQL 기본 및 활용·2장

DDL (CREATE / ALTER / DROP / TRUNCATE)

테이블·컬럼 정의, 제약조건, ALTER 변형, DROP과 TRUNCATE 차이까지 DDL 전반을 다룹니다.

1. CREATE TABLE

CREATE TABLE emp (
  emp_id    INT           PRIMARY KEY,
  name      VARCHAR(50)   NOT NULL,
  dept_id   INT,
  email     VARCHAR(200)  UNIQUE,
  hired_at  DATE          DEFAULT CURRENT_DATE,
  salary    NUMERIC(12,0) CHECK (salary >= 0),
  CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES dept(dept_id)
);

컬럼 이름, 타입, 제약을 한 번에 정의.
외래키는 컬럼 레벨 또는 테이블 레벨 CONSTRAINT로 명명.
이름 붙인 제약은 나중에 ALTER TABLE ... DROP CONSTRAINT로 관리하기 쉬움.

CTAS — CREATE TABLE AS SELECT

CREATE TABLE emp_backup AS SELECT * FROM emp WHERE hired_at < DATE '2024-01-01';

데이터와 구조를 함께 복제.
제약조건·인덱스는 일반적으로 복제되지 않는다 (Oracle은 NOT NULL만 복제).

2. ALTER TABLE

컬럼 추가 / 수정 / 삭제

ALTER TABLE emp ADD  middle_name VARCHAR(30);
ALTER TABLE emp MODIFY name VARCHAR(100);          -- Oracle
ALTER TABLE emp ALTER COLUMN name TYPE VARCHAR(100); -- PostgreSQL
ALTER TABLE emp DROP COLUMN middle_name;
ALTER TABLE emp RENAME COLUMN name TO full_name;

제약 추가 / 삭제

ALTER TABLE emp ADD CONSTRAINT uq_emp_email UNIQUE (email);
ALTER TABLE emp DROP CONSTRAINT uq_emp_email;

테이블 이름 변경

ALTER TABLE emp RENAME TO employee;  -- Oracle/PG
-- MySQL: RENAME TABLE emp TO employee;

3. DROP vs TRUNCATE vs DELETE

구분	분류	구조	데이터	롤백	트리거
`DROP TABLE`	DDL	삭제됨	삭제됨	불가	무관
`TRUNCATE TABLE`	DDL	유지	전부 삭제	불가	발화 X
`DELETE FROM`	DML	유지	조건에 따라 삭제	가능	발화 O

"DDL은 자동 커밋되어 롤백이 불가능하다" — SQLD 단골 출제.

TRUNCATE TABLE emp;          -- 모든 행 즉시 제거, 구조 유지
DELETE FROM emp;             -- 행 제거(롤백 가능), 트리거 발화
DROP TABLE emp;              -- 테이블 자체 사라짐

4. 참조 무결성 옵션 (FK)

FOREIGN KEY (dept_id) REFERENCES dept(dept_id)
  ON DELETE CASCADE    -- 부모 삭제 시 자식도 삭제
  ON UPDATE SET NULL;  -- 부모 PK 변경 시 자식 FK를 NULL로

옵션	의미
`NO ACTION` / `RESTRICT`	참조가 있으면 삭제/변경 불가 (기본)
`CASCADE`	부모의 변경을 자식에 전파
`SET NULL`	자식의 FK를 NULL로
`SET DEFAULT`	자식의 FK를 DEFAULT 값으로

5. 뷰(VIEW)도 DDL로 생성

CREATE OR REPLACE VIEW v_emp_summary AS
SELECT dept_id, COUNT(*) AS cnt, AVG(salary) AS avg_sal
FROM emp GROUP BY dept_id;

뷰는 가상 테이블 — 물리 저장하지 않고 SELECT 정의만 저장.
GROUP BY/집계가 포함된 뷰는 일반적으로 INSERT/UPDATE가 제한.
뷰에 쿼리를 날리면 옵티마이저가 기본 테이블로 머지(merge) 해 실행.

6. 자주 출제되는 포인트

TRUNCATE = DDL, 롤백 불가, 트리거 미발화.
DROP = 구조까지 삭제.
ALTER로 컬럼을 삭제하면 제약도 함께 삭제.
뷰는 물리적으로 구현되어 있지 않다 — 전형적 오답: "뷰는 물리적으로 구현되어 있다" → 틀림.
FK ON DELETE CASCADE는 부모 삭제 시 자식 행도 삭제.

7. 요약 체크리스트

CREATE TABLE에서 모든 기본 제약을 쓸 수 있다.
ALTER TABLE로 컬럼/제약/이름을 변경하는 구문을 쓸 수 있다.
DROP vs TRUNCATE vs DELETE를 6가지 차원에서 구분할 수 있다.
FK의 4가지 참조 옵션 동작을 설명할 수 있다.
뷰와 기본 테이블의 저장 구조 차이를 설명할 수 있다.