무결성 제약 조건

애플리케이션의 검증은 친절한 오류 메시지를 만들어 주지만, 데이터가 들어오는 모든 경로를 통제하지는 못합니다. 배치 작업과 관리자의 SQL, 다른 서비스가 같은 테이블에 쓸 수 있기 때문입니다. 언제나 참이어야 하는 규칙은 마지막 관문인 데이터베이스에도 선언해야 합니다.

회원 게시판에서 없는 작성자의 게시글, 비정상적인 조회수, 같은 회원이 같은 요청 키로 등록한 중복 게시글은 저장되면 안 됩니다. 이 글에서는 세 규칙을 각각 외래 키와 CHECK, UNIQUE로 나타내고, 어떤 위반이 어떤 오류를 내는지 확인합니다. 실습은 앞 장의 테이블을 바꾸지 않도록 별도의 post_integrity_demo에서 하며, 누적 모델의 members에 회원이 적어도 한 명 있고 id가 999999인 회원은 없다고 전제합니다.

SELECT로 확인하고 INSERT하면 생기는 틈

INSERT 전에 회원을 조회했으니 안전하다고 보고 외래 키를 생략해 봅니다. 그러나 확인한 직후에 부모가 지워지거나 다른 경로가 INSERT하면 고아 행이 생기며, 검증과 쓰기 사이의 시간 차이는 애플리케이션 코드만으로 닫히지 않습니다.

DROP TABLE IF EXISTS post_integrity_bad;
CREATE TABLE post_integrity_bad (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  author_id BIGINT UNSIGNED NOT NULL,
  request_key VARCHAR(80) NOT NULL,
  created_at DATETIME(6) NOT NULL,
  view_count BIGINT NOT NULL,
  status VARCHAR(16) NOT NULL,
  PRIMARY KEY (id)
) ENGINE = InnoDB;

INSERT INTO post_integrity_bad
  (author_id, request_key, created_at, view_count, status)
VALUES
  (999999, 'req-bad-1', '2026-07-14 09:00:00.000000', -30, 'DONE');

SELECT * FROM post_integrity_bad;

id | author_id | request_key | created_at                 | view_count | status
1  | 999999    | req-bad-1   | 2026-07-14 09:00:00.000000 | -30        | DONE

문법과 타입만 맞으므로 없는 회원과 음수의 조회수, 허용하지 않은 상태가 한 행에 함께 저장되었고, SQL은 아무 오류도 내지 않았습니다. 오류가 없다는 것이 바로 오류입니다. BIGINT와 VARCHAR는 업무의 뜻을 모르므로 타입만으로는 고아 참조와 잘못된 상태를 막을 수 없고, author_id처럼 참조를 뜻하는 이름을 붙여도 외래 키는 저절로 생기지 않습니다.

이 한 행에서 규칙이 몇 개 깨졌는지 나누어 봅니다. 부모 키 999999가 실제 회원에 있는지는 안티 조인으로, 조회수와 상태가 허용하는 범위와 집합 안에 있는지는 값으로 확인합니다. 같은 회원과 request_key의 조합이 여러 번 저장될 수 있는지도 보고, 웹 요청 말고도 배치나 SQL 가져오기가 같은 검증을 거치는지 확인합니다. 애플리케이션의 SELECT는 사용자에게 알려 주기에는 쓸모 있지만 제약을 대신하지 못하며, 데이터베이스의 제약이 최종 방어선이 되고 서비스는 그 오류를 업무의 오류로 바꿉니다.

제약마다 지키는 규칙

기본 키는 행을 대표하는 식별과 NULL 없는 유일성을, UNIQUE는 다른 후보 키의 유일성을, 외래 키는 참조 대상이 있다는 것과 지우거나 바꿀 때의 규칙을 지킵니다. CHECK는 한 행 안의 값 범위와 열 사이의 조건을, NOT NULL은 값이 비어 있지 않다는 것을 나타냅니다. 제약을 많이 붙이는 것이 목표가 아니라, 요구 사항을 데이터베이스가 판단할 수 있는 불변식으로 옮기는 것이 목표입니다.

"한 회원이 같은 요청 키로 만든 게시글은 많아야 하나"처럼 여러 행을 보아야 하는 규칙은 UNIQUE나 별도의 모델, 트랜잭션으로 나타냅니다. id는 안정적인 기본 키로 두고 업무상의 중복은 별도의 UNIQUE로 지키며, 게시글이 있는 회원을 지울 때 막을지 소프트 삭제할지를 정합니다. 조회수는 정해 둔 범위 안이어야 하고 본문은 허용하는 길이를 지켜야 하며, 상태의 오타를 막되 상태를 더하는 일이 DDL의 변경인지 코드 테이블의 변경인지도 판단합니다.

이름을 붙인 제약을 선언한다

부모를 가리키는 외래 키와 글 등록의 요청 키, 조회수와 상태의 CHECK를 선언합니다. MySQL 8.4의 InnoDB는 CHECK를 실제로 검사하며, 위반한 문장 전체를 거절합니다. 제약에 이름을 붙여 두면 운영의 오류 로그만 보고도 어떤 규칙이 깨졌는지 알 수 있습니다.

DROP TABLE IF EXISTS post_integrity_demo;
CREATE TABLE post_integrity_demo (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  author_id BIGINT UNSIGNED NOT NULL,
  request_key VARCHAR(80) NOT NULL,
  title VARCHAR(80) NOT NULL,
  content VARCHAR(5000) NOT NULL,
  created_at DATETIME(6) NOT NULL,
  view_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
  status VARCHAR(16) NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT uq_integrity_author_request
    UNIQUE (author_id, request_key),
  CONSTRAINT fk_integrity_author
    FOREIGN KEY (author_id) REFERENCES members (id),
  CONSTRAINT chk_integrity_view_count
    CHECK (view_count <= 1000000000),
  CONSTRAINT chk_integrity_content
    CHECK (CHAR_LENGTH(content) BETWEEN 1 AND 5000),
  CONSTRAINT chk_integrity_status
    CHECK (status IN ('DRAFT', 'HIDDEN', 'PUBLISHED', 'DELETED'))
) ENGINE = InnoDB;

INSERT INTO post_integrity_demo
  (author_id, request_key, title, content, created_at, view_count, status)
SELECT id, 'req-1', '첫 게시글', '제약이 지키는 게시글 본문',
       '2026-07-14 09:00:00.000000', 30, 'PUBLISHED'
FROM members ORDER BY id LIMIT 1;

회원이 있으므로 이 INSERT ... SELECT는 가장 작은 id의 회원, 곧 1장에서 넣은 [email protected]의 글로 정상 행 하나를 저장합니다. 회원이 한 명도 없다면 이 INSERT는 오류 없이 0행을 넣으므로, 먼저 회원이 있는지 확인합니다. 음수의 조회수는 열이 UNSIGNED이므로 1장 2편의 엄격 모드에서 범위 오류 1264로 거부되고, 상한은 CHECK가 지킵니다.

외래 키에는 자식 쪽 키의 인덱스가 필요합니다. 이 테이블에서는 UNIQUE (author_id, request_key)의 첫 열이 author_id이므로, MySQL은 외래 키를 위한 인덱스를 따로 만들지 않고 그 인덱스를 씁니다. 성능을 위해 만든 복합 인덱스도 왼쪽 접두어가 외래 키의 열을 제공하는지 확인해, 겹치는 단일 인덱스를 피합니다. 상태가 어떤 순서로 바뀔 수 있는지는 행 하나로 판단할 수 없으므로 서비스 트랜잭션의 규칙으로 남깁니다.

위반을 하나씩 재현한다

한 문장에 여러 위반을 섞으면 처음 걸린 오류만 보여, 어느 제약이 작동했는지 알기 어렵습니다. 다음 세 문장은 각각 하나의 제약만 어깁니다. 자동 커밋 상태에서 한 문장씩 실행하며, 실패한 문장은 행을 남기지 않습니다.

-- 1) 존재하지 않는 부모
INSERT INTO post_integrity_demo
  (author_id, request_key, title, content, created_at, view_count, status)
VALUES
  (999999, 'req-orphan', '고아 글', '부모 회원 없음',
   '2026-07-14 10:00:00', 30, 'PUBLISHED');

-- 2) 잘못된 조회수: 기존 회원 id를 사용한다.
INSERT INTO post_integrity_demo
  (author_id, request_key, title, content, created_at, view_count, status)
SELECT id, 'req-overflow', '조회수 오류', '상한 검사',
       '2026-07-14 11:00:00', 1000000001, 'PUBLISHED'
FROM members ORDER BY id LIMIT 1;

-- 3) 이미 저장한 author_id + request_key 중복
INSERT INTO post_integrity_demo
  (author_id, request_key, title, content, created_at, view_count, status)
SELECT id, 'req-1', '중복 요청', '멱등성 키 재사용',
       '2026-07-14 12:00:00', 45, 'PUBLISHED'
FROM members ORDER BY id LIMIT 1;

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`board_lab`.`post_integrity_demo`, CONSTRAINT `fk_integrity_author` ...)
ERROR 3819 (HY000): Check constraint 'chk_integrity_view_count' is violated.
ERROR 1062 (23000): Duplicate entry '1-req-1' for key 'post_integrity_demo.uq_integrity_author_request'

1452의 괄호 안에 이어지는 외래 키의 정의는 2장 4편처럼 앞부분만 남겼습니다. 1062의 메시지에는 복합 키의 값이 하이픈으로 이어져 '1-req-1'로 나타나며, 회원 1의 req-1이 이미 있다는 뜻입니다. 세 문장이 끝난 뒤 SELECT COUNT(*) FROM post_integrity_demo;로 세어 보면 정상 행 하나만 남아 있어야 합니다.

제약마다 반례를 직접 만들어 보면 이해가 깊어집니다. NULL을 허용하는 UNIQUE에 NULL 두 행을 넣어 보고, 꼭 있어야 하는 후보 키에 NOT NULL이 필요한 이유를 적습니다. 삭제의 규칙은 RESTRICT와 CASCADE를 별도의 실습 테이블에서 비교하고, 자동으로 붙은 제약의 이름과 업무를 담은 이름이 로그 분석에서 어떤 차이를 만드는지 확인합니다. 기존 데이터에 제약을 더하기 전에는 위반하는 행을 먼저 찾는 진단 쿼리를 씁니다.

NULL과 삭제의 규칙

MySQL의 UNIQUE 인덱스는 NULL을 허용하는 열에 NULL이 여러 개 들어가는 것을 허용합니다. 반드시 값이 있어야 하는 후보 키라면 NOT NULL을 함께 선언합니다. 외래 키의 NULL은 관계에 참여하지 않는다는 뜻이므로, 선택적인 참여라는 요구와 맞는지 따로 판단합니다.

CASCADE는 편리한 정리 기능이 아니라 데이터의 수명에 관한 규칙입니다. 회원을 지울 때 수년 동안의 게시글까지 함께 지워야 하는지, RESTRICT로 막은 뒤 익명화하고 보존해야 하는지는 감사의 요구와 함께 정합니다(16장 2편).

CHECK의 식에는 실행할 때마다 결과가 달라지는 함수나 다른 행을 조회하는 서브쿼리를 넣을 수 없으므로, 여러 행에 걸친 규칙은 다른 구조가 필요합니다. 외래 키와 참조되는 키의 열은 타입이 비슷해야 하는데, MySQL 8.4 문서에 따르면 정수나 DECIMAL은 크기와 부호가 같아야 하고, 문자열은 길이가 달라도 되지만 문자 집합과 정렬 규칙은 같아야 합니다. 부모 키의 변경을 ON UPDATE CASCADE로 허용하기 전에는 그 키가 정말 바뀔 수 있는 자연 키인지 다시 검토합니다.

대량으로 가져올 때 FOREIGN_KEY_CHECKS를 끄는 방식은, 참조의 검증을 별도의 일치 확인 단계로 옮기겠다는 뜻입니다. 다시 켠다고 그사이 들어온 행이 검사되지는 않으므로, 가져온 뒤 안티 조인으로 고아 행이 없는지 확인해야 합니다.

더 깊이: 제약은 문장의 끝이 아니라 행마다 검사된다

여러 행을 바꾸는 문장 하나에서 제약을 언제 검사하는지는 표준 SQL과 MySQL이 다릅니다. 표준 SQL의 기본 동작은 문장 전체를 처리한 뒤에 검사하는 것이지만, MySQL 8.4 매뉴얼에 따르면 MySQL은 외래 키와 UNIQUE를 행 하나를 처리할 때마다 검사합니다. 그래서 문장이 끝났을 때는 모든 규칙을 지키는 결과라도, 중간의 한 행이 규칙을 어기면 문장이 실패할 수 있습니다. 매뉴얼은 외래 키로 자기 자신을 가리키는 행을 지울 수 없다는 것을 이 차이의 예로 듭니다.

UNIQUE에서도 같은 일이 생깁니다. 고유 인덱스가 있는 id 열에 1과 2가 있을 때 UPDATE t SET id = id + 1은, 1을 2로 바꾸는 순간 아직 바뀌지 않은 2와 겹쳐 중복 키 오류가 날 수 있습니다. 매뉴얼은 ORDER BY id DESC를 붙여 큰 값부터 바꾸도록 순서를 정하는 방법을 안내합니다. 결과가 행을 처리하는 순서에 따라 달라질 수 있다는 점이, 행마다 검사하는 방식의 특징입니다.

같은 UPDATE 문장 안의 대입도 MySQL만의 순서를 따릅니다. 테이블 하나를 바꾸는 UPDATE의 대입은 대체로 왼쪽부터 평가되므로, UPDATE t1 SET col1 = col1 + 1, col2 = col1에서 col2는 원래 값이 아니라 방금 늘어난 col1의 값을 받습니다. 매뉴얼은 이 동작이 표준 SQL과 다르다고 밝히고, 여러 테이블을 바꾸는 UPDATE에서는 대입의 순서를 보장하지 않습니다.

기존 테이블에 제약을 더하는 순서

이미 큰 테이블에 제약을 더하면 전체를 검증하는 비용과 잠금이 생길 수 있습니다. 배포하기 전에 위반하는 행의 수를 0으로 만들고, DDL의 알고리즘과 복제 서버의 지연을 확인합니다(2장 1편).

제약의 오류율이 갑자기 늘면 버그나 규칙의 불일치를 알리는 신호입니다. 오류를 무조건 500으로 처리하지 말고 409나 422 같은 업무의 응답으로 나누고, 제약의 이름으로 지표를 집계합니다.

제약은 문장 하나의 불변식을 지키지만, 여러 문장을 하나의 업무로 묶지는 못합니다. 그 일은 다음 글의 트랜잭션이 맡습니다.

데이터베이스가 지금의 행과 참조의 키로 판단할 수 있고 언제나 참이어야 하는 규칙은 제약으로 선언합니다. 사용자에게 알려 주기 위한 사전 검증은 그것과 함께 두되, 최종적으로 위반될 가능성을 인정하고 데이터베이스의 오류를 처리합니다.

정리

• 타입과 애플리케이션의 검증만으로는 업무의 무결성이 완성되지 않습니다.
• 기본 키와 UNIQUE, 외래 키, CHECK, NOT NULL은 서로 다른 불변식을 지킵니다.
• 제약에 이름을 붙이면 오류 1452, 3819, 1062가 어느 규칙에서 났는지 바로 알 수 있습니다.
• 위반은 하나씩 재현하고, 오류가 난 뒤 잘못된 행이 남지 않았는지 확인합니다.
• NULL을 허용하는 UNIQUE와 CASCADE 같은 삭제의 규칙은 데이터의 수명과 함께 설계합니다.

연습 문제

1. 회원별 일일 게시 한도

한 회원은 같은 날짜, 같은 게시판에 일일 게시 한도를 한 번만 설정할 수 있고, 한도는 1건에서 20건 사이여야 합니다. member_daily_post_quota 테이블의 기본 키와 UNIQUE, 외래 키, CHECK를 설계하고, 제약마다 위반하는 SQL을 하나씩 써 보세요.

DROP TABLE IF EXISTS member_daily_post_quota;
CREATE TABLE member_daily_post_quota (
  quota_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  member_id BIGINT UNSIGNED NOT NULL,
  board_id BIGINT UNSIGNED NOT NULL,
  quota_date DATE NOT NULL,
  max_posts TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (quota_id),
  CONSTRAINT uq_quota_member_date_board
    UNIQUE (member_id, quota_date, board_id),
  CONSTRAINT fk_quota_member
    FOREIGN KEY (member_id) REFERENCES members (id),
  CONSTRAINT chk_quota_posts CHECK (max_posts BETWEEN 1 AND 20)
) ENGINE = InnoDB;

START TRANSACTION;
INSERT INTO member_daily_post_quota
  (member_id, board_id, quota_date, max_posts)
VALUES (1, 77, '2026-07-14', 10);

-- 같은 회원·게시판·날짜: 두 번째 INSERT는 1062 오류
INSERT INTO member_daily_post_quota
  (member_id, board_id, quota_date, max_posts)
VALUES (1, 77, '2026-07-14', 15);

-- 오류 뒤 별도로 실행해 첫 INSERT도 되돌린다.
ROLLBACK;

START TRANSACTION;
-- 999999가 members에 없고 다른 값은 정상: 1452 오류
INSERT INTO member_daily_post_quota
  (member_id, board_id, quota_date, max_posts)
VALUES (999999, 77, '2026-07-14', 10);
ROLLBACK;

START TRANSACTION;
-- 존재하는 회원·새 후보 키지만 한도0은 범위 밖: 3819 오류
INSERT INTO member_daily_post_quota
  (member_id, board_id, quota_date, max_posts)
VALUES (1, 77, '2026-07-14', 0);
ROLLBACK;

2. NULL이 여러 개인 UNIQUE

별도의 실습 데이터베이스에 NULL을 허용하는 UNIQUE 열을 만들고 (NULL), (NULL), ('A'), ('A')를 차례로 넣으면 어떤 문장이 실패하나요? 아래 테이블의 code에는 UNIQUE만 있고 NOT NULL은 없으므로, NULL 값 하나를 넣는 것 자체는 허용됩니다.

CREATE DATABASE IF NOT EXISTS cs_db_lab;
DROP TABLE IF EXISTS cs_db_lab.cs_unique_null;
CREATE TABLE cs_db_lab.cs_unique_null (
  code VARCHAR(10) NULL,
  UNIQUE KEY uq_cs_unique_null_code (code)
) ENGINE = InnoDB;

INSERT INTO cs_db_lab.cs_unique_null VALUES (NULL);
INSERT INTO cs_db_lab.cs_unique_null VALUES (NULL);
INSERT INTO cs_db_lab.cs_unique_null VALUES ('A');
INSERT INTO cs_db_lab.cs_unique_null VALUES ('A');

다음 글 트랜잭션과 멱등성에서는 제약을 모두 통과한 여러 문장이 중간의 오류 때문에 잘못된 업무 결과를 남기는 문제를 트랜잭션으로 해결하고, 같은 요청을 다시 보내도 결과가 한 번만 반영되게 하는 방법을 다룹니다.