SQL DDL | 1000PAGE

한 줄 정의

DDL(Data Definition Language)은 데이터의 값이 아니라 테이블·컬럼·제약 조건 같은 저장 구조를 만들고 바꾸고 없애는 SQL이다.

게임 서버가 “플레이어 1은 회복 물약 20개를 가진다”를 저장하려면 플레이어, 아이템 정의, 보유 수량의 구조부터 정해야 한다. CREATE는 구조를 만들고, ALTER는 운영 중 구조를 바꾸며, DROP은 구조와 그 데이터를 제거한다.

DDL과 DML을 먼저 구분한다

분류	역할	대표 명령
DDL	데이터 구조 정의	CREATE, ALTER, DROP
DML	구조 안의 행 조회·변경	SELECT, INSERT, UPDATE, DELETE

PostgreSQL에서는 대부분의 DDL도 트랜잭션 안에서 실행할 수 있다. 그러나 ALTER TABLE은 강한 잠금을 얻을 수 있고 큰 테이블의 검증·재작성은 오래 걸릴 수 있으므로, 문법상 가능한 것과 운영상 안전한 것은 구분해야 한다.

데이터 타입은 저장할 값의 계약이다

타입	게임 예시	선택 이유
bigint	player_id, item_id	많은 행의 정수 식별자
text	nickname, item_name	임의 길이 문자열
integer	level, quantity	범위 검사가 쉬운 정수
numeric(12,2)	현금 상점 가격	십진 금액을 정확히 저장
timestamptz	획득 시각	절대 시점을 시간대와 함께 처리

돈을 real이나 double precision에 저장하면 이진 부동소수점 반올림이 생길 수 있다. decimal과 numeric은 PostgreSQL에서 같은 정확한 십진 타입이다. 아이템 강화 확률처럼 근삿값 계산이 목적이면 부동소수점도 가능하지만 결제 금액에는 numeric을 우선한다.

CREATE TABLE: 전체 스키마 만들기

아래 스크립트는 PostgreSQL의 빈 데이터베이스에서 순서대로 실행할 수 있다. 인벤토리는 플레이어와 아이템 사이의 다대다 관계를 풀어낸 연결 테이블이다.

DROP TABLE IF EXISTS inventory;
DROP TABLE IF EXISTS items;
DROP TABLE IF EXISTS players;

CREATE TABLE players (
  player_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nickname text NOT NULL UNIQUE,
  level integer NOT NULL DEFAULT 1 CHECK (level >= 1),
  gold bigint NOT NULL DEFAULT 0 CHECK (gold >= 0),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE items (
  item_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  item_code text NOT NULL UNIQUE,
  item_name text NOT NULL,
  max_stack integer NOT NULL DEFAULT 1 CHECK (max_stack >= 1),
  shop_price numeric(12,2) CHECK (shop_price >= 0),
  tradable boolean NOT NULL DEFAULT true
);

CREATE TABLE inventory (
  player_id bigint NOT NULL
    REFERENCES players(player_id) ON DELETE CASCADE,
  item_id bigint NOT NULL
    REFERENCES items(item_id) ON DELETE RESTRICT,
  quantity integer NOT NULL DEFAULT 1 CHECK (quantity >= 1),
  acquired_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (player_id, item_id)
);

CREATE INDEX inventory_by_item ON inventory (item_id);

제약 조건 여섯 가지를 게임 규칙에 연결한다

PRIMARY KEY와 FOREIGN KEY

PRIMARY KEY는 행을 유일하게 식별하며 NULL을 허용하지 않는다. inventory의 복합 기본 키는 같은 플레이어와 아이템 조합이 한 행만 존재하게 한다. FOREIGN KEY는 참조 대상 행이 실제로 존재하게 하는 참조 무결성 규칙이다. 플레이어 삭제 때 보유 행은 CASCADE로 함께 지우지만, 아이템 정의 삭제는 보유자가 있으면 RESTRICT로 막는다.

UNIQUE와 NOT NULL

UNIQUE는 닉네임과 아이템 코드 중복을 막는다. PostgreSQL의 기본 UNIQUE 제약에서는 NULL 값 여러 개가 허용될 수 있으므로, 값 자체가 필수인 컬럼은 NOT NULL도 함께 선언한다. NOT NULL은 “알 수 없음” 또는 “아직 없음”이라는 NULL을 허용하지 않는다.

CHECK와 DEFAULT

CHECK는 한 행의 값이 조건을 만족하는지 검사한다. 수량 q와 최대 중첩량 m의 기본 불변식은 다음과 같다.

1\le q\le m

그러나 q는 inventory에, m은 items에 있으므로 단순 CHECK로 두 테이블을 비교할 수 없다. 이 규칙은 트랜잭션 로직이나 트리거로 강제해야 한다. 현재 DDL의 CHECK(quantity >= 1)는 한 행 안에서 보장할 수 있는 절반만 맡는다. DEFAULT는 INSERT에서 값을 생략했을 때 값을 채우지만, 명시적 NULL을 NOT NULL 대신 치료하지는 않는다.

정상 입력과 실패 입력으로 검증한다

INSERT INTO players (nickname, gold) VALUES ('루나', 5000);
INSERT INTO items (item_code, item_name, max_stack, shop_price)
VALUES ('POTION_HP_S', '소형 회복 물약', 99, 50.00);
INSERT INTO inventory (player_id, item_id, quantity) VALUES (1, 1, 20);

SELECT p.nickname, i.item_code, v.quantity
FROM inventory AS v
JOIN players AS p USING (player_id)
JOIN items AS i USING (item_id);

 nickname |  item_code  | quantity
----------+-------------+---------
 루나     | POTION_HP_S |       20

-- 각각 따로 실행하며 오류를 확인한다.
INSERT INTO players (nickname) VALUES ('루나');
-- UNIQUE 위반: nickname 중복

INSERT INTO players (nickname, level) VALUES ('초보자', 0);
-- CHECK 위반: level >= 1

INSERT INTO inventory (player_id, item_id, quantity) VALUES (999, 1, 1);
-- FOREIGN KEY 위반: player_id 999 없음

INSERT INTO items (item_code, item_name) VALUES ('BROKEN', NULL);
-- NOT NULL 위반: item_name

실패 예 전체를 한 트랜잭션에서 연속 실행하면 첫 오류 뒤 나머지 명령도 거부된다. 하나씩 실행하거나 SAVEPOINT를 사용해 각 제약 조건을 독립적으로 시험한다.

ALTER TABLE: 운영 중 요구사항 반영하기

시즌 업데이트로 아이템에 희귀도가 필요해졌다고 하자. 기존 행이 있는 테이블에 NOT NULL 컬럼을 바로 추가하면 기존 행을 채울 값이 없어 실패하거나, 변경 방식에 따라 긴 잠금과 테이블 재작성이 생길 수 있다. 확장-이행-축소 순서로 호환성을 유지한다.

안전한 확장-이행-축소

-- 1. 확장: 기존 애플리케이션도 동작하도록 NULL 허용 컬럼 추가
ALTER TABLE items ADD COLUMN rarity text;

-- 새·옛 서버가 함께 뜨는 배포 구간에는 새 서버가 rarity를 기록한다.

-- 2. 이행: 큰 테이블이면 한 번에 전부 갱신하지 말고 PK 범위로 나눈다.
UPDATE items SET rarity = 'COMMON'
WHERE rarity IS NULL AND item_id BETWEEN 1 AND 10000;

-- 모든 범위를 처리한 뒤 검증한다.
SELECT count(*) AS missing_rarity FROM items WHERE rarity IS NULL;

-- 3. 제약을 먼저 등록하고 나중에 검증하면 검증 시점을 통제할 수 있다.
ALTER TABLE items ADD CONSTRAINT items_rarity_allowed
  CHECK (rarity IN ('COMMON', 'RARE', 'EPIC', 'LEGENDARY')) NOT VALID;
ALTER TABLE items VALIDATE CONSTRAINT items_rarity_allowed;
ALTER TABLE items ALTER COLUMN rarity SET NOT NULL;
ALTER TABLE items ALTER COLUMN rarity SET DEFAULT 'COMMON';

NOT VALID은 기존 행 전체 검사를 나중으로 미루지만 제약 추가 후 새로 들어오거나 바뀌는 행에는 CHECK를 적용한다. VALIDATE도 잠금이 전혀 없는 것은 아니므로 운영 부하와 대기 시간을 관찰한다. DEFAULT를 먼저 둘지 마지막에 둘지는 애플리케이션 배포 순서와 “값 생략 시 의미”에 따라 결정한다.

마이그레이션 비용을 수식으로 생각하기

N개 행을 한 번에 갱신할 때 처리량이 초당 R행이라면 순수 작업 시간의 거친 하한은 다음과 같다.

T_{min}\approx\frac{N}{R}

실제 시간에는 잠금 대기, WAL 기록, 인덱스 갱신, 복제 지연이 더해진다. 그래서 행 수가 많은 백필은 작은 배치로 나누고 각 배치 사이에서 지표를 확인한다. 실행 전에는 백업보다 복원 가능성을 확인하고, 마이그레이션의 전진 수정과 롤백 절차를 함께 준비한다.

DROP: 데이터까지 사라지는 명령

DROP COLUMN과 DROP TABLE은 구조와 데이터를 제거한다. CASCADE는 의존 객체까지 연쇄 삭제하므로 운영 스키마에서 습관적으로 쓰지 않는다. 먼저 의존성과 애플리케이션 사용 여부를 확인하고, 두 번의 배포에 걸쳐 읽기·쓰기를 끊은 뒤 제거하는 축소 단계를 둔다.

-- 실습 데이터베이스에서만 실행한다.
BEGIN;
ALTER TABLE items DROP COLUMN rarity;
-- 의도한 변경인지 확인한 뒤 COMMIT, 아니면 ROLLBACK
ROLLBACK;

-- 테이블 전체 제거. CASCADE를 쓰지 않아 예상 밖 의존성은 오류로 드러낸다.
-- DROP TABLE inventory;

IF EXISTS는 “없으면 무시”하는 편의 기능이지 삭제 안전장치가 아니다. 잘못된 이름이나 환경을 조용히 지나가게 할 수 있으므로 배포 마이그레이션에서는 기대 상태를 검증하는 편이 낫다.

안전한 DDL 체크리스트

타입은 화면 모양이 아니라 값의 범위·정밀도·의미로 선택한다.
DB가 알 수 있는 규칙은 PK, FK, UNIQUE, CHECK, NOT NULL로 강제한다.
정상 INSERT뿐 아니라 중복·음수·고아 참조 같은 실패 INSERT도 테스트한다.
ALTER 전 잠금 수준, 테이블 크기, WAL·복제 영향, 실행 시간을 점검한다.
확장→애플리케이션 전환→백필·검증→축소 순서로 구버전과 신버전의 공존 구간을 만든다.
DROP 전에 실제 사용 중단과 복원 절차를 확인하고 CASCADE 범위를 명시적으로 검토한다.

참고 자료

PostgreSQL 공식 문서 — Data Types (2026-08-11 확인)
PostgreSQL 공식 문서 — Constraints (2026-08-11 확인)
PostgreSQL 공식 문서 — CREATE TABLE (2026-08-11 확인)
PostgreSQL 공식 문서 — ALTER TABLE (2026-08-11 확인)
PostgreSQL 공식 문서 — DROP TABLE (2026-08-11 확인)

댓글을 불러오는 중…