컬럼 타입은 그 컬럼에 어떤 값이 들어갈 수 있는지를, 제약 조건은 그 값이 지켜야 할 규칙을 정한다. 둘 다 애플리케이션 코드가 아니라 DB가 검사하므로, 어떤 코드가 쓰든 같은 규칙이 적용된다.

sql
CREATE TABLE bookings
(
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id  BIGINT       NOT NULL,
    status     ENUM ('PENDING', 'CONFIRMED', 'CANCELED') NOT NULL DEFAULT 'PENDING',
    amount     DECIMAL(10, 2) NOT NULL,
    memo       TEXT,
    booked_at  DATETIME     NOT NULL,
    created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (member_id) REFERENCES members (id)
);

CREATE TABLE은 소괄호를 쓰고, 컬럼 이름이 타입보다 먼저 온다. 컬럼 이름은 소문자와 밑줄(member_id)로 쓰는 것이 관례다.

컬럼 타입

정수

  • BIGINT: AUTO_INCREMENT 기본 키처럼 계속 늘어나는 식별자에 쓴다. 외래 키 컬럼도 가리키는 컬럼과 타입이 같아야 하므로 BIGINT로 맞춘다.
  • INT: 약 21억까지 담는다. 초 단위 시간처럼 그보다 커질 일이 없는 값에 쓴다.

문자열

  • VARCHAR(n): 최대 길이를 정한 가변 길이 문자열이다. UUID(36자)처럼 길이를 예측할 수 있는 값에 쓴다.
  • TEXT: 최대 65,535바이트까지 담는 긴 문자열이다. 길이가 들쭉날쭉하고 길어질 수 있는 값에 쓴다.
  • ENUM('A', 'B'): 정해 둔 값 중 하나만 허용한다. VARCHAR에는 오타도 그대로 저장되지만, ENUM은 목록 밖 값을 Data truncated for column 'status' 에러로 막는다.

ENUM은 대소문자를 구분하지 않고 정의된 표기로 바꿔 저장한다. 'confirmed'를 넣으면 'CONFIRMED'로 저장된다. 애플리케이션 enum 이름과 DB 쪽 철자가 다르면 저장은 되는데 읽을 때 실패할 수 있다. JPA 엔티티 매핑에 사례를 정리했다.

소수

  • DECIMAL(전체 자릿수, 소수 자릿수): 정확한 고정소수점이다. 금액, 거리처럼 정확해야 하는 값에 쓴다.
  • FLOAT, DOUBLE: 부동소수점이라 미세한 오차가 생긴다. MySQL에서도 0.1e0 + 0.2e0은 0.30000000000000004다.

애플리케이션의 Double을 DECIMAL 컬럼에 넣을 때 생기는 반올림 문제는 Kotlin 숫자와 문자열에 정리했다.

시각

  • DATETIME: 시간대 변환 없이 날짜와 시각 값 자체를 담는다. 예약 시각처럼 업무에서 의미 있는 시각에 쓴다.
  • TIMESTAMP: 저장할 때 UTC로 바꾸고 읽을 때 세션 시간대로 되돌린다. 2038년 1월 19일 이후 값은 담지 못한다. 행 생성, 수정 시각 같은 메타데이터에 관례적으로 쓴다.

제약 조건

NOT NULL

값이 없으면 저장을 막는다. '이 컬럼은 반드시 있어야 의미가 성립한다'는 의도를 스키마에 드러낸다.

  • 항상 있어야 하는 컬럼은 NOT NULL로 둔다.
  • 나중에 채워지거나, 아직 만들지 않은 기능 때문에 지금 채울 수 없는 컬럼은 NULL을 허용한다.

처음 테이블을 만들 때는 필수 컬럼에 바로 걸어도 부담이 없다. 운영 중인 테이블에 나중에 추가하면 기존 행을 채우는 단계가 필요하다. 그 방법은 ddl-auto와 Flyway에 정리했다.

DEFAULT

INSERT에서 그 컬럼을 아예 적지 않았을 때만 쓰이는 값이다. 값을 직접 적으면 그 값이 우선한다.

sql
INSERT INTO bookings (member_id, amount, booked_at) VALUES (7, 10000, NOW());
-- status를 생략했으므로 'PENDING', created_at도 생략했으므로 INSERT 시점의 시각

CURRENT_TIMESTAMP는 고정된 값이 아니라 INSERT가 실행된 시점의 시각이다. 기본값은 편하지만, 코드가 값을 빠뜨려도 조용히 기본값이 들어가 실수가 드러나지 않는다. 매번 코드가 정해야 하는 값에는 기본값을 두지 않는다.

PRIMARY KEY, FOREIGN KEY

  • PRIMARY KEY: 각 행을 구분하는 값이다. 중복과 NULL을 허용하지 않는다.
  • FOREIGN KEY (컬럼) REFERENCES 테이블(컬럼): 다른 테이블에 실제로 있는 값만 넣을 수 있게 한다. 두 컬럼의 타입이 정확히 같아야 한다.

CHECK

행을 저장하거나 고칠 때 조건이 참이어야만 통과시킨다. MySQL은 8.0.16부터 실제로 검사한다. 그 전 버전은 문법만 받아들이고 무시했다.

'상태가 DONE이면 본문이 있어야 한다'처럼 'A이면 B'인 규칙은 SQL에 '이면'이 없으므로 'A가 아니거나 B'로 바꿔 쓴다.

sql
CREATE TABLE results
(
    id     BIGINT AUTO_INCREMENT PRIMARY KEY,
    status ENUM ('PROCESSING', 'DONE', 'FAILED') NOT NULL,
    body   TEXT,
    CONSTRAINT chk_results_done_has_body
        CHECK (status <> 'DONE' OR (body IS NOT NULL AND body <> ''))
);

DONE이 아니면 앞쪽이 참이라 무조건 통과하고, DONE이면 뒤쪽이 참이어야 통과한다. MySQL 8.4에서 실행한 결과는 이렇다.

넣은 행결과
PROCESSING, NULL저장됨
DONE, 'ok'저장됨
DONE, NULL거절: Check constraint 'chk_results_done_has_body' is violated.
DONE, ''거절
DONE, ' '저장됨
  • IS NOT NULL만 쓰면 빈 문자열이 통과하므로 body <> ''를 함께 둔다.
  • 공백만 있는 값은 여전히 통과한다. TRIM까지 넣으면 조건이 길어지므로, 이런 값은 애플리케이션 코드에서 isBlank()로 거른다.
  • CONSTRAINT 이름을 주지 않으면 results_chk_1처럼 이름이 자동으로 붙는다. 에러 메시지만 보고 어떤 규칙인지 알 수 있게 이름을 붙인다.
  • 조건은 같은 행의 컬럼만 볼 수 있다. 다른 행이나 다른 테이블은 보지 못한다.
  • MySQL에는 CHECK 제약 이름만 바꾸는 명령이 없다. 바꾸려면 DROP CHECK 후 같은 조건으로 다시 추가한다.

어떤 규칙을 DB 제약으로 두고 어떤 규칙을 코드 검사로 둘지는 기준이 있다. 누가 쓰든 반드시 지켜져야 하는 데이터의 상태 정의는 DB 제약으로 둔다. 사용자 입력이 잘못된 경우는 코드에서 검사해 400 같은 응답으로 돌려준다.

공식 문서: MySQL 8.4, CHECK Constraints