데이터베이스 기초집LESSON 09

생성일·수정일·삭제 상태 등 공통 컬럼 설계

난이도초급 → 중급

예상 시간35분

선수지식이전 강의

9강. 생성일·수정일·삭제 상태 등 공통 컬럼 설계

1. 이번 강의에서 해결할 문제

모든 테이블에 습관적으로 created_at, updated_at, deleted_at을 붙이면 누가 언제 갱신하는지와 삭제된 행을 어떻게 제외할지가 불명확해집니다. 필요한 감사·동시성·보존 요구에서 공통 컬럼을 선택합니다.

2. 학습 목표

3. 핵심 개념

created_at은 생성 감사, updated_at은 마지막 변경 추적, version은 낙관적 동시성 제어에 사용합니다. 소프트 삭제는 복구와 감사를 돕지만 모든 조회에서 제외 조건이 필요하고 FK 대상이 실제로 남아 복잡성이 커집니다. 보존 의무가 없는 임시 데이터는 하드 삭제가 더 단순할 수 있습니다.

4. 단계별 예시

파일 경로: C:\dev\game-ranking-service\database\sql\09-common-columns.sql

database/sql/09-common-columns.sql
ALTER TABLE ranking.players
    ADD COLUMN IF NOT EXISTS created_at timestamptz NOT NULL DEFAULT now(),
    ADD COLUMN IF NOT EXISTS updated_at timestamptz NOT NULL DEFAULT now(),
    ADD COLUMN IF NOT EXISTS deleted_at timestamptz,
    ADD COLUMN IF NOT EXISTS version integer NOT NULL DEFAULT 1,
    ADD CONSTRAINT ck_players_version CHECK (version > 0);

-- 활성 닉네임만 유일하게 유지한다.
ALTER TABLE ranking.players DROP CONSTRAINT IF EXISTS players_nickname_key;
CREATE UNIQUE INDEX IF NOT EXISTS uq_players_active_nickname
ON ranking.players (lower(nickname))
WHERE deleted_at IS NULL;

PowerShell · 변경 전 백업과 적용
pg_dump -d game_ranking_dev -Fc -f .\backups\before-09.dump
psql -d game_ranking_dev -v ON_ERROR_STOP=1 --single-transaction -f .\database\sql\09-common-columns.sql

5. 동작 원리

부분 UNIQUE 인덱스는 deleted_at IS NULL인 활성 행만 유일성 검사에 포함합니다. 삭제된 닉네임을 다시 사용할 수 있지만 과거 행은 보존됩니다. updated_at은 PostgreSQL이 자동 갱신하지 않으므로 애플리케이션, SQLAlchemy 이벤트, 트리거 중 한 소유자를 정해야 합니다.

6. 자주 하는 실수와 해결법

7. 직접 실습

8. 이해 점검 질문 3개

9. 핵심 요약

MINI QUIZ