복합 키와 일대다 관계

"한 사용자는 하루에 게시 한도를 하나만 가진다"는 규칙은 member_id만으로도, quota_date만으로도 나타낼 수 없습니다. 두 값을 함께 보아야 한 행을 구별할 수 있으며, 이런 요구가 복합 키(composite key)의 출발점입니다.

같은 시점에 회원 게시판에는 관계가 하나 더 필요합니다. 게시글 카테고리 하나에는 여러 게시글이 쌓이고, 게시글 하나는 카테고리 하나에 속합니다. 복합 유일성과 일대다 관계를 함께 설계하면, "무엇이 한 행을 구별하는가"와 "외래 키를 어느 쪽에 둘 것인가"가 자연스럽게 이어집니다. 이 글은 앞 글에서 다시 만든 누적 모델에 이어서 실행합니다.

복합 후보 키의 최소성

복합 키는 열 두 개 이상을 묶은 키입니다. 열이 많다고 복합 후보 키가 되는 것은 아니며, 조합 전체가 유일하고 그 가운데 하나라도 빼면 유일성이 깨져야 최소성을 만족합니다. 일일 게시 한도인 daily_post_quota의 후보를 표로 검토해 봅시다.

허용하는 한도의 행 세 개가 (회원 1, 7월 14일, 5개), (회원 1, 7월 15일, 3개), (회원 2, 7월 14일, 4개)라고 해 봅시다. 회원만 남기면 앞의 두 행을, 날짜만 남기면 첫째와 셋째 행을 구별하지 못하지만, 회원과 날짜를 함께 보면 세 행이 모두 구별됩니다. max_posts는 이미 찾은 한도 행의 값일 뿐 식별에 필요하지 않습니다. 같은 회원과 날짜의 한도를 5에서 3으로 바꾸는 것은 기존 행의 UPDATE이고, 그 변경을 새 INSERT로 나타내려 하면 아래의 복합 UNIQUE가 거부합니다.

복합 키의 열 순서는 유일성 자체에는 영향을 주지 않지만, 인덱스를 쓰는 방식에는 영향을 줍니다. MySQL의 다중 열 B+Tree 인덱스는 왼쪽부터 이어지는 열의 조건을 중심으로 쓰이므로(7장 4편), UNIQUE (member_id, quota_date)는 특정 회원의 날짜별 한도를 찾는 데는 자연스럽지만, 모든 회원의 특정 날짜 한도만 자주 찾는다면 별도의 인덱스가 필요할 수 있습니다. 키는 먼저 업무의 규칙으로 정하고, 인덱스의 순서와 추가 인덱스는 실제 조회의 패턴으로 정합니다. "인덱스에 좋을 것 같다"는 이유로 업무상 유일성의 뜻을 바꾸지 않습니다.

복합 키와 대리 키

daily_post_quota의 정체성을 (member_id, quota_date)로 직접 나타내는 복합 기본 키의 모델도 충분히 유효합니다. 아래 DDL은 이름 끝의 _demo처럼 누적 모델과 따로 비교하려고 만드는 테이블로, id 열 없이 PRIMARY KEY (member_id, quota_date)가 두 열의 조합으로 행을 구별합니다. 기본 키의 첫 열이 member_id이므로, 외래 키 fk_quota_composite_member는 기본 키 인덱스를 그대로 쓰고 별도의 인덱스가 필요하지 않습니다.

DROP TABLE IF EXISTS daily_post_quota_composite_demo;
CREATE TABLE daily_post_quota_composite_demo (
  member_id BIGINT UNSIGNED NOT NULL,
  quota_date DATE NOT NULL,
  max_posts SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (member_id, quota_date),
  CONSTRAINT fk_quota_composite_member
    FOREIGN KEY (member_id)
    REFERENCES members (id)
) ENGINE = InnoDB;

다른 테이블이 이 행을 거의 참조하지 않고, 회원과 날짜의 조합이 수명 동안 바뀌지 않는다면 이 모델이 간결합니다. 반대로 한도의 변경 이력이나 알림처럼 여러 자식이 생기면, 모든 외래 키가 두 열을 되풀이해서 가져야 합니다. 한도의 날짜를 잘못 넣어 고치는 일도 대표 식별자의 변경이 됩니다.

회원 게시판의 누적 모델에서는 참조가 늘어날 것을 생각해 대리 키를 기본 키로 두고, 복합 후보 키는 UNIQUE로 지킵니다.

USE board_lab;

SET @member_id := (
  SELECT id
  FROM members
  ORDER BY id
  LIMIT 1
);

DROP TABLE IF EXISTS daily_post_quota;

CREATE TABLE daily_post_quota (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  member_id BIGINT UNSIGNED NOT NULL,
  quota_date DATE NOT NULL,
  max_posts SMALLINT UNSIGNED NOT NULL,
  note VARCHAR(300) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_daily_post_quota_member_date (member_id, quota_date),
  KEY ix_daily_post_quota_date (quota_date),
  CONSTRAINT ck_daily_post_quota_max_posts
    CHECK (max_posts BETWEEN 1 AND 20),
  CONSTRAINT fk_daily_post_quota_member
    FOREIGN KEY (member_id)
    REFERENCES members (id)
) ENGINE = InnoDB;

INSERT INTO daily_post_quota
  (member_id, quota_date, max_posts, note)
VALUES
  (@member_id, '2026-07-14', 5, '하루에 작성할 수 있는 게시글 수');

같은 회원과 날짜로 한도를 한 번 더 저장하면, 대리 키는 새 값을 만들 수 있어도 복합 대체 키가 거부합니다. 앞 코드는 id를 AUTO_INCREMENT 대리 키로 두고 (member_id, quota_date)에 UNIQUE를 걸었으며, 첫 회원의 7월 14일 한도 5를 한 행 넣었습니다. 아래 INSERT는 같은 회원과 같은 날짜에 한도만 3으로 바꾼 행입니다.

INSERT INTO daily_post_quota
  (member_id, quota_date, max_posts)
VALUES
  (@member_id, '2026-07-14', 3);

ERROR 1062 (23000): Duplicate entry '1-2026-07-14' for key 'daily_post_quota.uk_daily_post_quota_member_date'

@member_id는 앞 글에서 새로 만든 첫 회원이므로 1이고, 메시지의 값은 회원 id와 날짜를 하이픈으로 이은 것입니다. 대리 키를 썼다고 복합 키가 사라진 것은 아닙니다. 대표 기본 키의 역할은 id가, "회원마다 하루에 한도 하나"라는 업무 후보 키의 역할은 (member_id, quota_date)가 맡았습니다.

일대다 관계의 외래 키

이제 게시글 카테고리 categories를 더합니다. "데이터베이스" 카테고리 하나에는 여러 게시글이 들어갈 수 있고, 게시글은 저마다 정확히 카테고리 하나에 속합니다.

카테고리의 행에 post_ids = '1,2,3' 같은 목록을 넣으면 한 열에 여러 값이 들어가 원자성을 잃고, post_id_1, post_id_2처럼 열을 늘리면 게시글이 늘 때마다 스키마를 바꾸어야 합니다. 카테고리를 여러 행으로 복제하면 카테고리 기본 키의 유일성이 깨집니다. 반면 게시글은 저마다 속한 카테고리 하나만 알면 되므로, 외래 키 category_id는 다 쪽인 posts에 둡니다.

CREATE TABLE categories (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  category_code VARCHAR(40) NOT NULL,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(300) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_category_code (category_code),
  UNIQUE KEY uk_category_name (name)
) ENGINE = InnoDB;

INSERT INTO categories (category_code, name, description)
VALUES
  ('database', '데이터베이스', '관계형 모델과 MySQL 8.4 실습'),
  ('java', 'Java', '언어와 JVM 학습');

SET @database_category_id := (
  SELECT id
  FROM categories
  WHERE category_code = 'database'
);

ALTER TABLE posts
  ADD COLUMN category_id BIGINT UNSIGNED NULL AFTER author_id;

UPDATE posts
SET category_id = @database_category_id
WHERE category_id IS NULL;

데이터가 이미 있는 테이블에는 NULL을 허용한 채 열을 더하고, 값을 채우고, 누락을 확인한 뒤 필수 제약으로 강화하는 순서로 옮겨 갑니다(2장 1편). 운영 중인 테이블이라면 DDL의 잠금과 온라인 변경의 지원, 배치 갱신을 따로 검토하고, MySQL의 DDL은 암묵적으로 커밋하므로 일반 DML처럼 전체를 ROLLBACK할 수 있다고 가정하지 않습니다.

SELECT COUNT(*) AS missing_category_count
FROM posts
WHERE category_id IS NULL;

missing_category_count
0

결과가 0이므로 NOT NULL로 강화합니다. 아래 ALTER TABLE은 한 문장 안에서 세 가지를 합니다. category_id를 NOT NULL로 바꾸고, 카테고리별 게시글을 작성 시각 순서로 찾을 (category_id, created_at) 인덱스를 더하고, categories를 가리키는 외래 키를 겁니다. 이 인덱스의 첫 열이 category_id이므로 외래 키도 이 인덱스를 함께 씁니다.

ALTER TABLE posts
  MODIFY category_id BIGINT UNSIGNED NOT NULL,
  ADD KEY ix_posts_category_created (category_id, created_at),
  ADD CONSTRAINT fk_posts_category
    FOREIGN KEY (category_id)
    REFERENCES categories (id);

이관은 문법을 실행하는 일이 아니라, 강한 제약을 적용할 수 있는 데이터의 상태를 먼저 만드는 일입니다.

관계와 조회의 방향

객체는 참조 필드가 있는 방향으로만 이동하지만, 관계형 데이터베이스의 외래 키는 조회의 방향을 제한하지 않습니다. posts.category_id 하나로 게시글에서 카테고리를 찾을 수도, 카테고리에서 게시글의 목록을 찾을 수도 있습니다.

SELECT
  s.id AS post_id,
  s.title,
  sub.name AS category_name
FROM posts AS s
JOIN categories AS sub
  ON sub.id = s.category_id
WHERE s.author_id = @member_id
ORDER BY s.created_at;

SELECT
  sub.id AS category_id,
  sub.name,
  COUNT(s.id) AS post_count,
  COALESCE(SUM(s.view_count), 0) AS total_views
FROM categories AS sub
LEFT JOIN posts AS s
  ON s.category_id = sub.id
GROUP BY sub.id, sub.name
ORDER BY sub.id;

post_id | title            | category_name
1       | 대리 키의 책임   | 데이터베이스
2       | 업무 키의 유일성 | 데이터베이스

category_id | name         | post_count | total_views
1           | 데이터베이스 | 2          | 90
2           | Java         | 0          | 0

게시글은 저마다 카테고리 하나만 가리키므로, 게시글을 기준으로 한 다대일 조인은 기준의 행 수를 늘리지 않습니다. 반대로 카테고리를 기준으로 게시글을 조인하면 한 카테고리의 행이 게시글의 수만큼 되풀이될 수 있는데, 이것은 오류가 아니라 일대다 조인의 정상적인 모양입니다.

지금의 게시글 두 행은 모두 데이터베이스로 채웠으므로 집계는 게시글 2개, 조회수 50과 40을 더한 90입니다. Java에는 게시글이 없어도 LEFT JOIN이 카테고리의 행을 남기며, 그 행의 s.id는 NULL이어서 COUNT(s.id)는 0이고 SUM의 NULL은 COALESCE가 0으로 바꿉니다. COUNT(*)로 바꾸면 자식이 없는 Java도 조인 결과 한 행을 세어 1이 됩니다(5장 3편). 여러 일대다 자식을 한꺼번에 조인한 뒤 합계를 내면 행이 서로 곱해질 수 있으므로, 집계의 단위를 먼저 줄이거나 따로 집계한 결과를 조인합니다.

선택적인 참여와 NULL

posts.category_id의 NOT NULL과 외래 키는 모든 게시글이 있는 카테고리 하나에 반드시 참여하게 강제합니다. 반대로 새 카테고리는 게시글이 하나도 없어도 저장할 수 있는데, categories에는 "자식이 최소 한 행 있어야 한다"를 검사할 외래 키의 열이 없기 때문입니다.

"카테고리를 만들 때 첫 게시글도 반드시 있어야 한다"가 업무의 규칙이라면, 보통 애플리케이션이 두 행을 한 트랜잭션으로 만듭니다.

START TRANSACTION;

INSERT INTO categories (category_code, name)
VALUES ('network', '네트워크');

SET @network_category_id := LAST_INSERT_ID();

INSERT INTO posts
  (author_id, category_id, title, content, status,
   created_at, published_at, request_key)
VALUES
  (@member_id, @network_category_id, 'OSI 계층 정리', 'OSI 7계층의 책임을 정리합니다.', 'PUBLISHED',
   '2026-07-14 15:00:00.000000', '2026-07-14 15:00:00.000000', 'post-key-network');

COMMIT;

두 INSERT가 모두 성공한 경우에만 COMMIT하고, 가운데 문장이 실패하면 전체를 ROLLBACK합니다. 일반적인 문장의 오류가 언제나 트랜잭션 전체를 저절로 취소하지는 않습니다(8장 2편). 이 posts에는 공개 상태와 게시 시각의 조합을 검사하는 CHECK가 아직 없으므로, 공개 글에는 게시 시각을 직접 함께 넣었습니다.

ERD는 "카테고리는 최소 한 게시글을 가진다"는 규칙을 나타낼 수 있지만, 기본적인 외래 키만으로 부모 쪽의 최소 개수까지 강제할 수는 없습니다. 모델과 DDL, 애플리케이션이 각각 무엇을 보장하는지 구별해야 합니다.

정리

• 복합 후보 키는 조합 전체가 유일하고, 열마다 모두 필요해야 합니다.
• 대리 기본 키를 고르더라도, 복합 업무 규칙은 UNIQUE로 지킵니다.
• 일대다 관계의 외래 키는 부모 하나를 가리킬 수 있는 다 쪽에 둡니다.
• 외래 키 하나로 양쪽 방향의 조인이 가능하며, 일에서 다로 조인하면 행이 늘어날 수 있습니다.
• 자식의 필수 참여는 NOT NULL과 외래 키로 강제할 수 있지만, 부모의 최소 자식 수는 별도의 업무 로직이 필요합니다.

연습 문제

1. 게시글 안의 댓글 순번

게시글 하나에는 순서가 있는 댓글이 여러 개 생깁니다. 댓글의 번호는 게시글 안에서 1, 2, 3으로 시작하며, 다른 게시글에서는 같은 번호를 다시 쓸 수 있습니다. 다른 테이블이 댓글을 따로 참조할 계획은 아직 없습니다. 후보 키를 고르고, 외래 키를 어느 테이블에 둘지 설명한 뒤, 복합 기본 키를 쓴 연습용 DDL을 만들어 같은 게시글의 같은 순번을 두 번 넣어 보세요.

SET @comment_post_id := (
  SELECT MIN(id)
  FROM posts
  WHERE author_id = @member_id
);

DROP TABLE IF EXISTS comments;
CREATE TABLE comments (
  post_id BIGINT UNSIGNED NOT NULL,
  author_id BIGINT UNSIGNED NOT NULL,
  sequence_no SMALLINT UNSIGNED NOT NULL,
  content VARCHAR(1000) NOT NULL,
  status VARCHAR(16) NOT NULL DEFAULT 'VISIBLE',
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (post_id, sequence_no),
  CONSTRAINT ck_comments_sequence CHECK (sequence_no >= 1),
  CONSTRAINT ck_comments_status
    CHECK (status IN ('VISIBLE', 'HIDDEN', 'DELETED')),
  CONSTRAINT fk_comment_post
    FOREIGN KEY (post_id)
    REFERENCES posts (id),
  CONSTRAINT fk_comment_author
    FOREIGN KEY (author_id)
    REFERENCES members (id)
) ENGINE = InnoDB;

INSERT INTO comments (post_id, author_id, sequence_no, content)
VALUES (@comment_post_id, @member_id, 1, '후보 키를 이해했습니다.');

INSERT INTO comments (post_id, author_id, sequence_no, content)
VALUES (@comment_post_id, @member_id, 1, '같은 순번 중복');

ERROR 1062 (23000): Duplicate entry '1-1' for key 'comments.PRIMARY'

2. Java 카테고리의 COUNT(*)

본문의 카테고리별 집계에서 COUNT(s.id)를 COUNT(*)로 바꾸면 결과는 어떻게 되나요? 네트워크 카테고리를 더한 지금의 상태에서 답해 보세요.

다음 글 일대일과 다대다 관계에서는 관계의 나머지 형태인 일대일과 다대다를, 회원 게시판의 프로필과 태그 모델로 완성합니다.