백엔드 데이터 인프라 23편 — 제약 깊이 CHECK DEFERRABLE EXCLUDE

스마트라이프onLeave a Comment on 백엔드 데이터 인프라 23편 — 제약 깊이 CHECK DEFERRABLE EXCLUDE
2026-05-17•백엔드 데이터 인프라

백엔드 데이터 인프라 23편. 제약 깊이 — 복합 CHECK 표현식·DEFERRABLE·EXCLUDE·복합 키와 운영 함정 풀어쓴 학습 노트.

📚 백엔드 데이터 인프라 · 23편 — 제약 깊이 CHECK DEFERRABLE EXCLUDE

이 글은 백엔드 데이터 인프라 시리즈 70편 중 23편이에요. 9편 CREATE TABLE ·15편 외래 키 에서 기본 제약을 다뤘죠. 이번 23편은 PG가 가진 더 강력한 제약 도구들 — CHECK 표현식·DEFERRABLE·EXCLUDE를 짚어요.

제약의 5가지 종류

9편 에서 본 6개 제약을 분류해 보면 이렇습니다.

제약 의미 분류
NOT NULL 값 필수 컬럼
UNIQUE 중복 X 컬럼·테이블
PRIMARY KEY NOT NULL + UNIQUE + 인덱스 테이블
FOREIGN KEY 참조 무결성 테이블
CHECK 조건 검증 컬럼·테이블
EXCLUDE 범위·조건 충돌 방지 테이블

EXCLUDE는 PG만의 특별한 제약이라 다른 RDBMS에는 없어요. 23편 후반에서 깊이 짚어요.

CHECK 제약 깊이

컬럼 수준

CREATE TABLE products (
    id    BIGSERIAL PRIMARY KEY,
    price INTEGER CHECK (price >= 0),
    stock INTEGER CHECK (stock >= 0),
    grade CHAR(1) CHECK (grade IN ('A', 'B', 'C'))
);

한 컬럼에 대한 단순 검증이에요.

테이블 수준 — 여러 컬럼 조합

CREATE TABLE date_ranges (
    id    BIGSERIAL PRIMARY KEY,
    start_date DATE NOT NULL,
    end_date   DATE NOT NULL,
    CHECK (end_date >= start_date)
);

여러 컬럼 사이의 관계는 컬럼 수준이 아니라 테이블 수준 CHECK로 박아야 해요.

함수 호출 CHECK

CREATE TABLE users (
    id    BIGSERIAL PRIMARY KEY,
    email TEXT CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$')
);

정규식·내장 함수·사용자 함수 모두 쓸 수 있어요. 단 — STABLE(같은 트랜잭션 안 결과 고정) 또는 IMMUTABLE(항상 같은 결과) 함수만 가능합니다. 성능·복제 때문이에요.

NOT VALID — 사후 추가

15편 외래 키 에서 다룬 NOT VALID 패턴이 CHECK에도 똑같이 적용돼요.

-- 1. 새 데이터에만 검증
ALTER TABLE products ADD CONSTRAINT positive_price
    CHECK (price >= 0) NOT VALID;

-- 2. 백그라운드 검증
ALTER TABLE products VALIDATE CONSTRAINT positive_price;

큰 테이블에 사후 제약을 추가해도 다운타임이 0이에요.

명명

CONSTRAINT chk_products_price CHECK (price >= 0)

명시적으로 이름을 박아두면 ERROR 메시지나 DROP 시점에 무엇을 가리키는지 명확해져요. 큰 시스템을 운영할 때는 표준으로 박는 편이 좋아요.

CHECK vs 트리거

CHECK로 풀 수 있는 건 한 행을 한 시점에 검증하는 경우예요. 다른 행이나 다른 테이블을 참조해야 하는 검증이라면 트리거를 써야 해요.

-- CHECK 불가능 (다른 행 참조)
-- "같은 시간에 같은 사용자가 주문 2개 못 함"

-- 트리거로 구현
CREATE FUNCTION check_duplicate_order()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (SELECT 1 FROM orders
               WHERE user_id = NEW.user_id
               AND created_at = NEW.created_at
               AND id <> NEW.id) THEN
        RAISE EXCEPTION '중복 주문';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_check_duplicate
BEFORE INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION check_duplicate_order();

DEFERRABLE — 트랜잭션 끝에 검증

15편 에서 짧게 본 옵션을 더 깊이 짚어요.

즉시 vs 지연

CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id)
    DEFERRABLE INITIALLY DEFERRED      -- 기본 = 지연
    -- 또는 DEFERRABLE INITIALLY IMMEDIATE  -- 즉시 (DEFERRABLE 단어만 박힌 의미)

기본 동작은 세 가지로 나뉘어요.

  • NOT DEFERRABLE (기본) = 매 SQL마다 즉시 검증
  • DEFERRABLE INITIALLY IMMEDIATE = 즉시지만 SET CONSTRAINTS로 지연 가능
  • DEFERRABLE INITIALLY DEFERRED = 트랜잭션 끝까지 미룸

순환 참조 시나리오

CREATE TABLE departments (
    id BIGINT PRIMARY KEY,
    manager_id BIGINT
);

CREATE TABLE employees (
    id BIGINT PRIMARY KEY,
    department_id BIGINT REFERENCES departments(id) DEFERRABLE
);

ALTER TABLE departments ADD CONSTRAINT fk_manager
    FOREIGN KEY (manager_id) REFERENCES employees(id) DEFERRABLE;

BEGIN;
SET CONSTRAINTS ALL DEFERRED;

INSERT INTO departments (id, manager_id) VALUES (1, 100);   -- 100 없음 — OK (지연)
INSERT INTO employees (id, department_id) VALUES (100, 1);  -- 둘 다 만족

COMMIT;   -- 여기서 한 번에 검증

순환 의존성을 처리할 때 꼭 필요한 패턴이에요.

비싼 검증 일괄

-- 한 트랜잭션 안 여러 UPDATE·INSERT, 검증은 한 번만
BEGIN;
SET CONSTRAINTS ALL DEFERRED;

UPDATE orders SET status = 'PAID' WHERE ...;
INSERT INTO order_logs SELECT id, 'PAID' FROM orders WHERE ...;
UPDATE inventory SET stock = stock - 1 WHERE ...;

COMMIT;

검증 N번을 1번으로 줄여줘서 큰 배치 작업에 잘 맞아요.

EXCLUDE 제약 — PG 특별

UNIQUE가 "같은 값 안 됨" 이라면, EXCLUDE는 "겹치는 범위·조건 안 됨" 이에요.

CREATE EXTENSION btree_gist;

CREATE TABLE bookings (
    id BIGSERIAL PRIMARY KEY,
    room_id BIGINT NOT NULL,
    period TSRANGE NOT NULL,
    EXCLUDE USING GIST (room_id WITH =, period WITH &&)
);

여기서 btree_gist는 일반 비교 연산자를 GIST 인덱스(범위·다차원 검색용 인덱스)에서도 쓸 수 있게 해주는 확장이에요. TSRANGE는 시작·끝 시각을 한 컬럼에 담는 타임스탬프 범위 타입.

  • room_id WITH = = 같은 방
  • period WITH && = 겹치는 기간

같은 방에 시간이 겹치는 예약을 넣으려고 하면 거부됩니다.

INSERT INTO bookings (room_id, period)
VALUES (1, '[2026-05-17 10:00, 2026-05-17 12:00)');   -- OK

INSERT INTO bookings (room_id, period)
VALUES (1, '[2026-05-17 11:00, 2026-05-17 13:00)');   -- ERROR! 겹침

예약·일정·재고 시스템에 매우 강력해요.

EXCLUDE WHERE — 조건부

EXCLUDE USING GIST (room_id WITH =, period WITH &&)
WHERE (canceled IS NOT TRUE)

취소된 예약은 검사 대상에서 빼요.

복합 키·복합 UNIQUE

CREATE TABLE order_items (
    order_id BIGINT,
    line_no  INTEGER,
    product_id BIGINT NOT NULL,
    PRIMARY KEY (order_id, line_no)
);

-- 또는 unique
CREATE TABLE user_tags (
    user_id BIGINT,
    tag     TEXT,
    UNIQUE (user_id, tag)
);

다중 컬럼 키는 JPA에서 @EmbeddedId로 매핑해요.

컬럼 순서가 중요

UNIQUE (a, b)   -- (a, b) 조합 unique. a 단독 unique X

복합 인덱스와 동작이 같아요. a 만 조회할 때는 인덱스를 활용하지만, b 만 조회할 때는 활용하지 못해요.

제약 관리

확인

\d users     -- psql 메타 — 제약 함께 표시

-- 또는 시스템 카탈로그
SELECT
    conname,
    pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'users'::regclass;

변경

ALTER TABLE users
    DROP CONSTRAINT chk_email,
    ADD CONSTRAINT chk_email CHECK (email ~ '...');

DROP + ADD가 표준이에요. 제약을 직접 ALTER로 바꾸지는 못해요.

일시 비활성화

ALTER TABLE users DISABLE TRIGGER ALL;       -- 모든 트리거 (제약 트리거 포함)
ALTER TABLE users ENABLE TRIGGER ALL;

위험 — 데이터 무결성이 깨질 수 있어서 데이터 이관·복구 시에만 써야 해요.

Spring·JPA 와의 매핑

자바 백엔드 입문 35편 Bean Validation 의 @Valid·@NotNull·@Email 같은 어노테이션이 DB CHECK 제약과 대응돼요.

@Entity
public class User {
    @Id
    @GeneratedValue
    private Long id;

    @Column(nullable = false)
    private String name;

    @Column(unique = true)
    @Email
    private String email;

    @Min(0)
    private Integer age;
}

JPA가 ddl-auto: update 모드에서 NOT NULL·UNIQUE는 자동으로 만들어줘요. 단 — @Email·@Min 은 DB CHECK로 자동 생성되지 않아요. Hibernate Validator가 앱 단에서 검증해 줄 뿐이에요.

룰: 비즈니스 룰 검증은 양쪽 다 박아요 — 앱(즉시 피드백) + DB(최후 방어선).

함정 5가지

(1) CHECK 에 VOLATILE 함수

CHECK (created_at = NOW())   -- ❌ NOW()는 VOLATILE

CHECK는 IMMUTABLE·STABLE 함수만 받아요. VOLATILE(호출할 때마다 결과가 바뀌는) 함수를 박으면 복제·재생성 시점에 문제가 터져요.

(2) DEFERRABLE 안 박고 SET CONSTRAINTS

SET CONSTRAINTS ALL DEFERRED;  -- DEFERRABLE 없는 제약은 영향 X

처음부터 DEFERRABLE을 박아둬야 SET이 동작해요.

(3) EXCLUDE + btree_gist 누락

EXCLUDE USING GIST (room_id WITH =, ...)
-- btree_gist 확장 없으면 ERROR

CREATE EXTENSION btree_gist; 를 먼저 박아야 해요.

(4) 복합 UNIQUE 순서 잘못

-- (a, b) UNIQUE 인덱스 박힌 상태
SELECT ... WHERE b = ...   -- 인덱스 활용 X

자주 검색하는 컬럼을 앞에 둬야 해요.

(5) 운영에 CHECK 직접 추가

ALTER TABLE huge_table ADD CONSTRAINT chk_x CHECK (...);
-- 큰 테이블 = 전체 검증, 락 폭주

NOT VALID + VALIDATE 2단계가 표준이에요.

🎯 제약 5단계

(1) NOT NULL·UNIQUE·PK·FK = 기본 4가지. (2) CHECK = 한 행 검증 (IMMUTABLE 함수). (3) 복잡 검증 = 트리거. (4) DEFERRABLE = 순환 참조·일괄 검증. (5) EXCLUDE = 범위·시간 충돌 방지. 앱 + DB 양쪽 검증.

한 줄 정리 — 제약 5종 + EXCLUDE. CHECK 깊이 = 테이블 수준·정규식·NOT VALID 2단계 추가. DEFERRABLE = 트랜잭션 끝 검증 (순환 참조). EXCLUDE = PG 특별, 범위·시간 충돌 방지. 운영 추가는 NOT VALID + VALIDATE 표준.

시험 직전 한 번 더 — 제약 깊이 입문자가 매번 헷갈리는 것

  • 제약 6종 = NOT NULL·UNIQUE·PRIMARY KEY·FOREIGN KEY·CHECK·EXCLUDE
  • CHECK = 한 행 검증 (단일 행 안 표현식)
  • 컬럼 수준 vs 테이블 수준 (여러 컬럼)
  • IMMUTABLE·STABLE 함수만 CHECK 가능
  • VOLATILE (NOW·random 등) = CHECK X
  • 정규식 CHECK = 이메일·전화번호 검증
  • 복잡 검증 = 트리거 (다른 행·테이블 참조)
  • DEFERRABLE = 트랜잭션 끝 검증
  • INITIALLY DEFERRED = 기본 지연
  • INITIALLY IMMEDIATE = 즉시 (SET 으로 지연 가능)
  • SET CONSTRAINTS ALL DEFERRED
  • 순환 참조 처리 = DEFERRABLE 필수
  • EXCLUDE = PG 특별 (범위·시간 충돌)
  • btree_gist 확장 필요
  • EXCLUDE USING GIST (col1 WITH op, col2 WITH op)
  • TSRANGE·INT4RANGE 활용
  • 예약·일정·재고 시스템에 강력
  • 복합 키 = PRIMARY KEY (a, b)
  • 복합 UNIQUE = 컬럼 순서가 인덱스 활용 결정
  • NOT VALID = 새 데이터만 검증
  • VALIDATE CONSTRAINT = 백그라운드 검증
  • 운영 추가 = NOT VALID + VALIDATE 2단계
  • JPA Bean Validation = 앱 단 검증 (DB와 동시)
  • @NotNull·@Email = 앱만, DB도 박아야 안전
  • 제약 명명 = chk_*·uniq_*·fk_*
  • 변경 = DROP + ADD (직접 ALTER X)

시리즈 다른 편 (앞뒤 글 모음)

이전 글:

다음 글:

※ 이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.

답글 남기기

error: Content is protected !!