SQL 데이터 타입과 제약조건 - PK, FK, UNIQUE, NOT NULL, CHECK 정리 (SQL 초급 4단원)

이 단원에서 배우는 것

1단원부터 3단원까지는 이미 들어 있는 데이터를 읽기만 했다. 그런데 3단원 끝에서 "숫자를 문자열 컬럼에 넣으면 정렬이 사전순이 된다"는 문제를 만났다. 조회가 이상하게 도는 원인이 조회문이 아니라 테이블 설계에 있는 경우다. 이번 단원은 조회에서 잠시 물러나, 컬럼 타입을 무엇으로 잡고 어떤 제약조건을 걸어야 나중에 데이터가 썩지 않는지를 본다. 다음 단원에서 실제로 데이터를 넣고 고칠 때, 여기서 건 제약이 방어선 역할을 한다.

숫자·문자·날짜 타입을 용도에 맞게 고르는 기준을 세운다.
PK·FK·UNIQUE·NOT NULL·CHECK 다섯 가지 제약조건이 각각 무엇을 막는지 구분한다.
돈을 실수 타입에 넣으면 왜 안 되는지 직접 확인한다.

개념

컬럼 타입은 저장 공간을 아끼려고 고르는 것이 아니다. 그 컬럼에 들어올 수 없는 값을 데이터베이스가 대신 거부하게 만드는 장치다. 나이 컬럼을 VARCHAR(10) 으로 잡으면 '스물다섯'도, 'abc'도, 빈 문자열도 다 들어간다. INT 로 잡으면 그 순간 세 가지가 다 막힌다. 애플리케이션에서 검증하면 되지 않느냐고 하지만, 한 테이블에 접근하는 경로는 웹 서버 하나가 아니다. 배치 스크립트, 관리자 콘솔, 이전 버전 서버, 손으로 친 UPDATE 가 다 들어온다. 그중 하나만 검증을 빠뜨려도 데이터는 오염된다. 마지막까지 남는 검증은 DB 쪽 제약조건뿐이다.

제약조건도 같은 관점으로 본다. NOT NULL 은 "값이 없는 상태"를, UNIQUE 는 "중복"을, FOREIGN KEY 는 "없는 부모를 가리키는 고아 행"을, CHECK 는 "말이 안 되는 값"을 막는다. 이 넷을 안 걸어 두면 6개월 뒤에 WHERE status IN (...) 조건에 무엇을 넣어야 하는지 아무도 모르게 된다.

표준 SQL 문법과 예제

타입 고르는 기준

용도	고르는 타입	이유와 주의점
식별자, 개수, 나이	`INT` / `BIGINT`	행이 20억을 넘길 여지가 있으면 처음부터 BIGINT 로 간다. 나중에 바꾸려면 테이블 전체를 다시 쓴다
금액, 수량, 비율	`DECIMAL(p, s)`	정확한 10진 연산. `FLOAT`·`DOUBLE` 은 절대 금지
측정값, 좌표	`DOUBLE` / `FLOAT`	오차가 허용되는 값만. 등호 비교를 하지 않는다
이름, 주소처럼 길이가 들쭉날쭉	`VARCHAR(n)`	n 은 실제 상한으로 잡는다. 습관적인 255 는 근거가 없다
국가코드처럼 길이가 고정	`CHAR(n)`	짧고 길이가 진짜 고정일 때만. 남는 자리를 공백으로 채운다
날짜만 필요	`DATE`	생일, 입사일
날짜 + 시각	`DATETIME` / `TIMESTAMP`	타임존을 어떻게 다룰지 먼저 정하고 고른다
참 / 거짓	`BOOLEAN` 또는 `CHAR(1)`	MySQL 의 BOOLEAN 은 `TINYINT(1)` 의 별칭이다

제약조건 다섯 가지

1단원에서 만든 테이블은 기본키만 걸린 최소 형태였다. 실무라면 이렇게 만든다.

CREATE TABLE dept (
    dept_id   INT         NOT NULL,
    dept_name VARCHAR(30) NOT NULL,
    location  VARCHAR(30),
    CONSTRAINT pk_dept        PRIMARY KEY (dept_id),
    CONSTRAINT uq_dept_name   UNIQUE (dept_name)
);

CREATE TABLE emp (
    emp_id    INT           NOT NULL,
    emp_name  VARCHAR(30)   NOT NULL,
    dept_id   INT,
    job       VARCHAR(20)   NOT NULL,
    salary    DECIMAL(10,2) NOT NULL,
    bonus     DECIMAL(10,2),
    hire_date DATE          NOT NULL,
    mgr_id    INT,
    CONSTRAINT pk_emp       PRIMARY KEY (emp_id),
    CONSTRAINT fk_emp_dept  FOREIGN KEY (dept_id) REFERENCES dept (dept_id),
    CONSTRAINT ck_emp_pay   CHECK (salary > 0),
    CONSTRAINT ck_emp_bonus CHECK (bonus IS NULL OR bonus >= 0)
);

이미 만들어 둔 테이블에는 ALTER 로 붙인다. 기존 데이터가 제약을 위반하면 이 문장 자체가 실패하므로, 붙이기 전에 위반 행을 먼저 조회해서 정리한다.

-- 붙이기 전 확인: 없는 부서를 가리키는 사원이 있는가
SELECT e.emp_id, e.dept_id
FROM   emp e
WHERE  e.dept_id IS NOT NULL
  AND  NOT EXISTS (SELECT 1 FROM dept d WHERE d.dept_id = e.dept_id);

ALTER TABLE emp
  ADD CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES dept (dept_id);

제약조건에 pk_·uq_·fk_·ck_ 같은 이름을 직접 붙이는 이유는 하나다. 위반 시 오류 메시지에 그 이름이 찍힌다. 이름을 안 주면 emp_chk_1 같은 자동 생성 이름이 나와서, 로그만 보고는 어떤 규칙이 깨졌는지 알 수 없다.

다섯 가지의 역할은 이렇게 나뉜다. NOT NULL 은 값 자체를 강제한다. UNIQUE 는 중복을 막지만 NULL 은 여러 개 허용한다(SQL Server 만 예외). PRIMARY KEY 는 NOT NULL 과 UNIQUE 를 합친 것이고 테이블당 하나뿐이다. FOREIGN KEY 는 부모 테이블에 있는 값만 허용하며, 부모를 지울 때 어떻게 할지를 ON DELETE 로 정한다. CHECK 는 임의의 조건식이다.

DB별 차이

항목	MySQL / MariaDB	PostgreSQL	Oracle	SQL Server
가변 문자열	`VARCHAR(n)`, n 은 문자 수	`VARCHAR(n)` / `TEXT`	`VARCHAR2(n)`, 기본은 바이트 수	`NVARCHAR(n)` (유니코드)
정확한 10진수	`DECIMAL(p,s)`	`NUMERIC(p,s)`	`NUMBER(p,s)`	`DECIMAL(p,s)`
날짜 + 시각	`DATETIME`, `TIMESTAMP`	`TIMESTAMPTZ` 권장	`DATE` 가 초 단위 시각까지 포함	`DATETIME2`
자동 증가 키	`AUTO_INCREMENT`	`GENERATED ... AS IDENTITY`	시퀀스 또는 12c 이상 IDENTITY	`IDENTITY(1,1)`
CHECK 실제 적용	MySQL 8.0.16+, MariaDB 10.2.1+	적용	적용	적용
UNIQUE 컬럼의 NULL	여러 개 허용	여러 개 허용	여러 개 허용	하나만 허용

실무에서 자주 틀리는 것

1. 금액을 FLOAT 이나 DOUBLE 에 넣는다

이진 부동소수점은 0.1 을 정확히 표현하지 못한다. 한 건에서는 안 보이지만 수십만 건을 SUM 하면 원 단위가 어긋나고, 회계와 대사가 맞지 않는다.

-- MySQL 에서 실행. 소수점 리터럴은 DECIMAL, 0.1e0 처럼 지수를 붙이면 DOUBLE 이다
SELECT 0.1 + 0.2 = 0.3       AS dec_ok,
       0.1e0 + 0.2e0 = 0.3e0 AS dbl_ok;
-- dec_ok = 1, dbl_ok = 0

돈·수량·비율은 예외 없이 DECIMAL(Oracle 은 NUMBER)로 잡는다. 정밀도는 통화 단위에 맞춘다. 원화라면 소수부가 필요 없으니 DECIMAL(13,0), 환율이나 외화가 섞이면 소수부를 준다.

2. MySQL 5.7 에서 CHECK 를 걸어 두고 걸렸다고 믿는다

MySQL 5.7 이하는 CHECK 절을 문법으로 받아 주기만 하고 아무 검사도 하지 않는다. 오류가 안 나니 잘 걸린 줄 알고 넘어간다. 8.0.16 부터 실제로 동작한다. 지금 쓰는 서버가 어느 쪽인지 모르겠으면 위반 값을 한 건 넣어 보는 것이 가장 빠르다. 들어가면 안 걸린 것이다.

3. utf8 로 만들어 놓고 이모지에서 막힌다

MySQL 의 utf8 은 문자당 최대 3바이트만 쓰는 별도 문자셋(utf8mb3)이라, 4바이트가 필요한 이모지와 일부 한자를 저장할 수 없다. Incorrect string value: '\xF0\x9F...' 오류가 이것이다. 새로 만드는 스키마는 무조건 utf8mb4 로 한다. MySQL 8.0 부터는 기본값이 utf8mb4 다.

CREATE TABLE memo (
    memo_id INT PRIMARY KEY,
    body    VARCHAR(500) NOT NULL
) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

콜레이션 이름은 서버마다 다르다. MariaDB 에는 utf8mb4_0900_ai_ci 가 없으므로 utf8mb4_general_ci 나 utf8mb4_uca1400_ai_ci 를 쓴다.

4. 외래키를 안 걸거나, CASCADE 를 남발한다

외래키가 없으면 부서를 지운 뒤에도 그 부서를 가리키는 사원 행이 남는다. 조인하면 사라지고 단독 조회하면 보이는 유령 데이터가 된다. 반대로 ON DELETE CASCADE 를 습관적으로 붙이면 부모 한 행을 지우는 순간 자식이 통째로 사라진다. 실무에서 안전한 기본값은 자식이 있으면 부모 삭제를 거부하는 쪽이고, 삭제 순서는 사람이 명시적으로 정한다. MySQL·PostgreSQL 은 ON DELETE RESTRICT 라고 쓰면 되고, Oracle 과 SQL Server 에는 RESTRICT 키워드가 없으므로 아무것도 안 쓰거나 ON DELETE NO ACTION 을 쓴다(둘 다 같은 동작이다).

스스로 확인하기

dept 테이블의 dept_name 에 중복이 들어가지 못하게 제약을 추가하라.
emp 의 hire_date 는 반드시 있어야 하고, salary 는 0보다 커야 한다. 두 규칙을 제약조건으로 표현하라.
주문 금액을 저장할 컬럼 타입을 고르고, 그 이유를 한 문장으로 설명하라.

-- 1
ALTER TABLE dept ADD CONSTRAINT uq_dept_name UNIQUE (dept_name);

-- 2
ALTER TABLE emp MODIFY hire_date DATE NOT NULL;   -- MySQL / MariaDB
-- PostgreSQL: ALTER TABLE emp ALTER COLUMN hire_date SET NOT NULL;
-- Oracle:     ALTER TABLE emp MODIFY (hire_date DATE NOT NULL);
ALTER TABLE emp ADD CONSTRAINT ck_emp_pay CHECK (salary > 0);

-- 3
-- DECIMAL(13,0). 금액은 오차가 허용되지 않으므로 이진 부동소수점을 쓰지 않는다.
-- 원화는 소수부가 없고, 13자리면 조 단위까지 담긴다.

MySQL 공식 문서 - Data Types