일대일과 다대다 관계

회원 게시판의 회원에게 선택적인 프로필을 붙이고, 게시글마다 여러 태그를 달고 싶습니다. 프로필은 회원과 일대일, 게시글과 태그는 다대다 관계입니다. 둘 다 선 하나를 그리는 일처럼 보이지만, 관계형 테이블에서는 전혀 다른 제약이 필요합니다.

일대일은 외래 키에 유일성을 더해 "많아야 하나"를 만들고, 다대다는 관계 자체를 한 행으로 올려 두 개의 일대다로 풉니다. 중요한 것은 기호를 외우는 것이 아니라, 관계의 행을 어디에 저장하고 어떤 중복을 금지할지 정하는 일입니다. 이 글도 10장 2편부터 쌓은 누적 모델에 이어서 실행합니다.

일대일 관계의 유일성

회원 한 명이 프로필을 많아야 하나만 가진다고 해 봅시다. member_profiles.member_id가 회원을 참조하는 외래 키이기만 하면, 같은 회원을 가리키는 프로필을 여러 행 저장할 수 있습니다. 외래 키는 "있는 회원인가"를 검사할 뿐, "몇 번 참조했는가"를 제한하지 않기 때문입니다.

일대일을 만들려면 외래 키의 열에 UNIQUE가 있거나, 그 열이 기본 키여야 합니다. 회원 게시판은 보조 테이블의 외래 키를 그대로 기본 키로 쓰는 공유 기본 키(shared primary key)의 방식을 고릅니다.

USE board_lab;

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

DROP TABLE IF EXISTS member_profiles;

CREATE TABLE member_profiles (
  member_id BIGINT UNSIGNED NOT NULL,
  bio VARCHAR(500) NULL,
  avatar_url VARCHAR(768) NULL,
  updated_at DATETIME(6) NOT NULL
    DEFAULT CURRENT_TIMESTAMP(6)
    ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (member_id),
  CONSTRAINT fk_member_profiles_member
    FOREIGN KEY (member_id)
    REFERENCES members (id)
) ENGINE = InnoDB;

INSERT INTO member_profiles
  (member_id, bio, avatar_url)
VALUES
  (@member_id, '게시판 운영자입니다.', 'https://cdn.example.com/avatar/min.png');

이 구조에서는 두 제약이 함께 작동합니다. 외래 키는 없는 회원의 프로필을 막고, 기본 키는 같은 회원의 두 번째 프로필을 막습니다.

INSERT INTO member_profiles
  (member_id, bio, avatar_url)
VALUES
  (@member_id, '중복 프로필', NULL);

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

이 모델에서 프로필은 선택적인 참여입니다. 회원의 행만 있고 프로필이 없어도 되지만, 프로필이 있다면 반드시 회원 하나를 가리킵니다. 모든 회원에게 프로필이 반드시 있어야 한다는 부모 쪽의 최소 참여는, 이 외래 키만으로는 강제되지 않습니다.

외래 키는 어느 쪽에 둘까

일대일은 양쪽 모두 최대가 하나이므로, 일대다처럼 다 쪽이 분명하지 않습니다. 보통은 수명과 의존성을 기준으로 고릅니다. 프로필이 회원 없이 존재할 수 없다면 보조 테이블인 member_profiles가 회원을 참조하고, 그러면 members에 NULL을 허용하는 프로필 id를 더하지 않아도 되며, 나중에 프로필의 이력이나 여러 공개 프로필로 넓힐 때도 주 테이블을 덜 바꿉니다.

반대로 보조 행이 언제나 먼저 존재하고 주 테이블이 그 행을 반드시 가져야 하거나, 특정 조회 경로에서 식별자가 직접 꼭 필요한 경우에는 주 테이블에 외래 키를 둘 수도 있습니다. 다만 "조인이 하나 줄 것 같다"는 짐작만으로 수명의 방향을 뒤집기보다, 실제의 쿼리 계획과 변경의 가능성을 확인합니다.

일대일의 분리는 개인정보의 접근 제어, 아주 큰 선택 열, 서로 다른 변경 빈도처럼 분명한 이유가 있을 때 쓸모가 있습니다. 단지 파일을 나누듯 테이블을 잘게 쪼개면, 조인과 무결성의 관리만 늘어날 수 있습니다.

다대다 관계의 연결 테이블

게시글 하나에는 mysql, modeling 같은 여러 태그가 달리고, 태그 하나는 여러 게시글에서 다시 쓰입니다. 게시글에 tag_ids = '1,2,5'를 저장하면 열 하나에 여러 값이 들어가고, JSON 배열을 쓰더라도 배열의 요소마다 tags.id를 가리키는 외래 키를 선언할 수 없어 참조 무결성을 얻지 못합니다. 태그에 post_ids 목록을 넣어도 같은 문제가 방향만 바뀝니다.

관계를 저장할 별도의 행을 만들면 문제가 단순해집니다. post_tags의 한 행은 게시글 하나와 태그 하나만 가리키므로, 그 결과는 posts 1:N post_tags와 tags 1:N post_tags라는 두 관계가 됩니다.

DROP TABLE IF EXISTS post_tags;
DROP TABLE IF EXISTS tags;

CREATE TABLE tags (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(60) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_tags_name (name)
) ENGINE = InnoDB;

CREATE TABLE post_tags (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  post_id BIGINT UNSIGNED NOT NULL,
  tag_id BIGINT UNSIGNED NOT NULL,
  source VARCHAR(20) NOT NULL DEFAULT 'MANUAL',
  attached_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_post_tags_pair (post_id, tag_id),
  KEY ix_post_tags_tag_post (tag_id, post_id),
  CONSTRAINT ck_post_tags_source
    CHECK (source IN ('MANUAL', 'AUTO')),
  CONSTRAINT fk_post_tags_post
    FOREIGN KEY (post_id)
    REFERENCES posts (id),
  CONSTRAINT fk_post_tags_tag
    FOREIGN KEY (tag_id)
    REFERENCES tags (id)
) ENGINE = InnoDB;

post_tags.id는 연결 행의 대표 식별자이고, (post_id, tag_id)는 "한 게시글에 같은 태그는 한 번만 붙인다"는 복합 후보 키이므로 UNIQUE로 남겼습니다. 연결 행을 다른 테이블이 참조하지 않는 단순한 모델이라면, 두 외래 키를 복합 기본 키로 삼는 선택도 가능합니다.

source와 attached_at은 게시글이나 태그 하나만의 속성이 아니라, 그 태그가 그 게시글에 붙었다는 관계에서만 뜻이 있습니다. 연결 테이블이 기술적인 다리가 아니라, 업무의 데이터를 담는 연관 엔티티가 되는 지점입니다.

연결의 제약을 확인한다

태그를 만들고 첫 번째 게시글에 이어 봅니다. 아래 코드는 태그 세 개를 만든 뒤, @member_id가 쓴 게시글 가운데 id가 가장 작은 글에 mysql과 key-design 두 태그를 INSERT ... SELECT로 한 번에 연결합니다. 마지막 SELECT는 게시글에서 연결 테이블을 거쳐 태그까지 두 번 JOIN해, 연결이 제대로 들어갔는지 확인합니다.

INSERT INTO tags (name)
VALUES ('mysql'), ('logical-modeling'), ('key-design');

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

INSERT INTO post_tags (post_id, tag_id, source)
SELECT @first_post_id, id, 'MANUAL'
FROM tags
WHERE name IN ('mysql', 'key-design');

SELECT
  s.id AS post_id,
  s.title,
  t.name AS tag_name,
  st.source,
  st.attached_at
FROM posts AS s
JOIN post_tags AS st
  ON st.post_id = s.id
JOIN tags AS t
  ON t.id = st.tag_id
WHERE s.id = @first_post_id
ORDER BY t.name;

post_id | title          | tag_name   | source
1       | 대리 키의 책임 | key-design | MANUAL
1       | 대리 키의 책임 | mysql      | MANUAL

게시글 하나가 연결 행 두 개를 거쳐 태그 두 개와 이어졌습니다. attached_at은 실행한 시각이 기본값으로 들어가므로 결과에서 뺐습니다. 이번에는 같은 연결을 다시 넣어 봅니다.

INSERT INTO post_tags (post_id, tag_id, source)
SELECT @first_post_id, id, 'AUTO'
FROM tags
WHERE name = 'mysql';

ERROR 1062 (23000): Duplicate entry '1-1' for key 'post_tags.uq_post_tags_pair'

게시글 1과 태그 1(mysql)의 쌍이 이미 있어 거부되었습니다. 대리 키 post_tags.id만 있었다면 이 중복도 서로 다른 행으로 저장되었을 것이므로, 복합 대체 키가 관계의 업무 유일성을 지킨 것입니다. 없는 태그의 id를 넣으면 이번에는 외래 키가 거부합니다.

INSERT INTO post_tags (post_id, tag_id, source)
VALUES (@first_post_id, 999999, 'MANUAL');

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`board_lab`.`post_tags`, CONSTRAINT `fk_post_tags_tag` ...)

유일성 제약은 "같은 관계가 몇 번 있을 수 있는가"를, 외래 키는 "관계의 양 끝이 실제로 있는가"를 각각 보장합니다.

관계의 속성과 양방향 탐색

특정 태그가 붙은 모든 게시글을 찾을 때는 태그에서 연결 테이블을 거쳐 게시글로 갑니다. 아래 조회는 앞의 조회와 같은 세 테이블을 쓰지만 출발점이 tags이고, JOIN도 태그, 연결 행, 게시글의 순서로 거꾸로 적었습니다. 조건은 태그 이름 하나뿐이므로, 그 태그가 붙은 게시글이 늘어나면 결과의 행도 그만큼 늘어납니다.

SELECT
  t.name AS tag_name,
  s.id AS post_id,
  s.title,
  s.created_at
FROM tags AS t
JOIN post_tags AS st
  ON st.tag_id = t.id
JOIN posts AS s
  ON s.id = st.post_id
WHERE t.name = 'mysql'
ORDER BY s.created_at DESC;

tag_name | post_id | title          | created_at
mysql    | 1       | 대리 키의 책임 | 2026-07-13 13:00:00.000000

외래 키는 post_tags에만 있지만 어느 방향으로도 조회할 수 있습니다. ix_post_tags_tag_post (tag_id, post_id)는 태그에서 시작하는 경로를 돕고, UNIQUE (post_id, tag_id)는 게시글에서 시작하는 경로에 왼쪽 접두어를 제공합니다. 실제로 어떤 인덱스를 고르고 몇 행을 읽는지는 데이터가 충분히 쌓인 뒤 EXPLAIN ANALYZE로 확인합니다.

관계의 속성이 늘어나면 이름도 다시 봅니다. post_tags에 검수 상태나 추천의 이유, 떼어 낸 시각이 더해진다면 "태그 부착"이라는 독립된 개념으로 다루어야 할 수 있습니다. 이름은 지금의 구현이 아니라, 행이 나타내는 업무의 사실을 드러내야 합니다.

관계의 유형을 고르는 순서

카디널리티는 지금 데이터의 개수가 아니라 허용할 수 있는 최대의 개수로 판단합니다. 지금 모든 회원이 프로필을 하나씩 가지고 있어도 프로필이 선택 사항이라면 최솟값은 0이고, 지금 태그가 하나만 붙어 있어도 앞으로 여러 태그를 허용한다면 최댓값은 N입니다.

고르는 순서는 이렇습니다. 양쪽에서 최소와 최대의 개수를 각각 적고, 외래 키가 있는 행 하나가 상대 행을 몇 개 가리켜야 하는지 봅니다. 일대일이면 외래 키의 유일성까지 선언하고, 일대다면 외래 키를 다 쪽에 두며, 다대다면 관계를 연결 행으로 바꾸어 두 개의 일대다로 풉니다. 마지막으로 관계 자체에서 생기는 시각과 상태, 순서, 수량 같은 속성이 없는지 다시 묻습니다.

일대일이 시간이 지나 일대다로 바뀔 가능성이 크다면, 처음부터 "지금 활성인 행은 하나"인 일대다 모델이 더 맞을 수 있습니다. 예를 들어 프로필의 수정 이력을 모두 남겨야 한다면, member_profiles 한 행을 덮어쓰기보다 member_profiles_revision의 여러 행과 지금의 버전을 가리키는 포인터를 설계합니다(15장). 카디널리티는 화면의 모양이 아니라 데이터의 수명 규칙에서 나옵니다.

정리

• 일대일은 외래 키에 UNIQUE나 기본 키의 제약이 더해져야 합니다.
• 의존하는 보조 테이블이 주 테이블을 참조하면, 선택 정보와 수명의 기준을 나누기 쉽습니다.
• 다대다는 어느 한쪽에 id 목록을 넣지 않고, 연결 테이블로 두 개의 일대다 관계로 풉니다.
• 연결 테이블의 복합 유일성은 관계의 중복을 막고, 외래 키는 양 끝이 있다는 것을 보장합니다.
• 시각과 상태, 순서처럼 관계에서 생긴 속성은 연결 테이블에 저장합니다.

연습 문제

1. 게시글과 첨부파일의 배치

게시글에는 첨부파일 여러 개가 이어지고, 같은 첨부파일은 여러 게시글에서 다시 쓰입니다. 이을 때마다 게시글 안에서 보여 줄 순서 position과 개인 메모 memo를 기록해야 합니다. 두 엔티티 사이의 카디널리티를 판단하고 관계의 속성을 고른 뒤, 한 게시글에서 같은 첨부파일을 두 번 잇지 못하고 보여 줄 순서도 겹치지 않게 DDL을 써 보세요.

CREATE TABLE IF NOT EXISTS attachments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  canonical_url VARCHAR(768) NOT NULL,
  title VARCHAR(200) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_attachments_canonical_url (canonical_url)
) ENGINE = InnoDB;

DROP TABLE IF EXISTS post_attachments;
CREATE TABLE post_attachments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  post_id BIGINT UNSIGNED NOT NULL,
  attachment_id BIGINT UNSIGNED NOT NULL,
  position SMALLINT UNSIGNED NOT NULL,
  memo VARCHAR(300) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_post_attachments_pair (post_id, attachment_id),
  UNIQUE KEY uk_post_attachments_position (post_id, position),
  CONSTRAINT ck_post_attachments_position CHECK (position >= 1),
  CONSTRAINT fk_post_attachments_post
    FOREIGN KEY (post_id) REFERENCES posts (id),
  CONSTRAINT fk_post_attachments_attachment
    FOREIGN KEY (attachment_id) REFERENCES attachments (id)
) ENGINE = InnoDB;

2. 프로필의 외래 키를 회원 쪽에 둔다면

member_profiles가 회원을 가리키는 대신, members에 profile_id 열을 두고 프로필을 가리키게 하면 무엇이 달라지나요?

이로써 회원 게시판의 회원과 외부 계정, 카테고리, 게시글, 프로필, 태그가 안정적인 키와 관계로 이어졌습니다. 다음 글 식별 관계와 비식별 관계에서는 부모의 키가 자식의 식별자에 들어가는지에 따라 관계를 나누고, 그 선택이 모델에 주는 영향을 봅니다.