자연 키와 대리 키

앞 글의 이메일을 기본 키로 삼은 모델은 참조 무결성 덕분에 잘못된 변경을 막았지만, 정상적인 이메일 변경까지 어렵게 만들었습니다. 이 글에서는 회원의 정체성은 members.id가 맡고, 이메일의 중복 금지는 UNIQUE 제약이 맡도록 역할을 나눕니다.

대리 키를 들인다는 것은 업무의 규칙을 버린다는 뜻이 아닙니다. 대표 식별자는 안정적인 값으로 고정하고, 업무에서 생긴 후보 키는 대체 키로 계속 지킨다는 뜻입니다.

자연 키와 대리 키

자연 키(natural key)는 이메일이나 ISBN, 국가 코드처럼 업무의 세계에 이미 있는 값입니다. 값을 보면 뜻을 알 수 있고, 조건이 맞으면 조인 없이도 업무의 대상을 바로 찾을 수 있습니다. 반면 정책이나 형식이 바뀌거나, 다시 발급되거나, 개인정보로 다루어야 하는 일이 식별의 체계까지 흔들 수 있습니다.

대리 키(surrogate key)는 행을 식별하려고 데이터베이스 안에서 붙인 값으로, 보통 숫자나 UUID이며 업무의 뜻을 담지 않습니다. 값이 작고 안정적이어서 참조하기 쉽지만, 대리 키만 더하면 이메일의 중복 같은 업무의 규칙을 놓치기 쉽습니다.

어느 한쪽이 언제나 정답은 아닙니다. 범위가 작고 변경을 관리하는 주체가 분명한 코드 테이블은 자연 키가 간결할 수 있습니다. 반면 회원과 게시글, 댓글처럼 수명이 길고 여러 테이블과 시스템이 참조하는 엔티티는, 대리 키를 대표 식별자로 두는 편이 변화에 강합니다.

누적 모델을 다시 만든다

이제 오류를 재현하던 테이블과 떨어진, 실제의 누적 모델을 다시 만듭니다. 아래 스크립트는 MySQL 8.4의 InnoDB를 기준으로 기존의 누적 테이블을 지우고 다시 만듭니다. 이 데이터를 비우고 모델을 새로 만드는 것은 실습의 단계이며, 운영 데이터를 옮기는 절차가 아닙니다.

앞 장의 실습을 같은 board_lab에서 이어 했다면, members나 posts를 참조하는 실습용 자식 테이블이 남아 있을 수 있습니다. 외래 키의 참조 검사 때문에 자식을 남긴 채 부모 테이블을 지울 수 없으므로, 그런 테이블을 먼저 정리합니다.

USE board_lab;
DROP TABLE IF EXISTS post_integrity_demo;
DROP TABLE IF EXISTS member_daily_post_quota;
DROP TABLE IF EXISTS daily_post_stats;
DROP TABLE IF EXISTS member_point_accounts;
DROP TABLE IF EXISTS tx_comment_demo;
DROP TABLE IF EXISTS daily_post_quota_composite_demo;
DROP TABLE IF EXISTS attachment_reference_demo;

이 목록은 8장의 실습과, 이 장의 본문과 연습에서 만드는 자식 테이블입니다. 그 밖에 직접 만든 참조 테이블이 있다면, 앞 글의 information_schema.REFERENTIAL_CONSTRAINTS 조회에서 부모를 members, posts, attachments로 바꾸어 먼저 확인합니다. 목록에 있으리라 짐작하고 참조 검사를 끄지 말고, 자식부터 정리합니다. 여기부터는 새 모델을 만들고, 같은 연결에서 INSERT와 LAST_INSERT_ID()를 차례로 실행합니다.

USE board_lab;

DROP TABLE IF EXISTS post_attachments;
DROP TABLE IF EXISTS attachments;
DROP TABLE IF EXISTS post_tags;
DROP TABLE IF EXISTS tags;
DROP TABLE IF EXISTS comments;
DROP TABLE IF EXISTS member_profiles;
DROP TABLE IF EXISTS daily_post_quota;
DROP TABLE IF EXISTS posts;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS member_external_identities;
DROP TABLE IF EXISTS members;

CREATE TABLE members (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(80) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_members_email (email),
  CONSTRAINT ck_members_status
    CHECK (status IN ('ACTIVE', 'SUSPENDED', 'DELETED'))
) ENGINE = InnoDB;

CREATE TABLE posts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  author_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(160) NOT NULL,
  content TEXT NOT NULL,
  status VARCHAR(16) NOT NULL DEFAULT 'DRAFT',
  view_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL
    DEFAULT CURRENT_TIMESTAMP(6)
    ON UPDATE CURRENT_TIMESTAMP(6),
  published_at DATETIME(6) NULL,
  version BIGINT UNSIGNED NOT NULL DEFAULT 0,
  request_key VARCHAR(64) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_posts_author_request (author_id, request_key),
  KEY ix_posts_author_created (author_id, created_at DESC, id DESC),
  CONSTRAINT ck_posts_status
    CHECK (status IN ('DRAFT', 'HIDDEN', 'PUBLISHED', 'DELETED')),
  CONSTRAINT fk_posts_author
    FOREIGN KEY (author_id)
    REFERENCES members (id)
) ENGINE = InnoDB;

이 posts의 CHECK는 상태의 목록뿐이며, 1장에서 더한 게시 시각의 조합이나 본문 길이의 CHECK는 이 새 테이블에 저절로 이어지지 않습니다. 최종 모델의 제약은 12장 4편에서 다시 모읍니다.

AUTO_INCREMENT는 MySQL의 생성 방식입니다. 대리 키라는 모델링의 개념은 DBMS와 관계없지만, PostgreSQL의 identity 열이나 SQL Server의 IDENTITY, 애플리케이션에서 만드는 UUID처럼 값을 발급하는 문법은 제품마다 다릅니다. 이 교재는 MySQL 인스턴스 하나에서 시작하는 회원 게시판에 맞춰 BIGINT UNSIGNED AUTO_INCREMENT를 기준으로 씁니다.

uq_members_email을 빼면, 대리 키가 서로 다른 두 행을 만들어 주므로 같은 이메일의 회원을 여러 번 저장할 수 있습니다. 대리 키는 행의 유일성을 보장할 뿐, 어떤 업무의 속성이 겹치면 안 되는지는 알지 못합니다.

식별자는 그대로 두고 이메일만 바꾼다

INSERT INTO members (email, password_hash, name)
VALUES ('[email protected]', '$2a$12$example.hash', '안동민');

SET @member_id := LAST_INSERT_ID();

INSERT INTO posts
  (author_id, title, content, status, view_count, created_at, published_at, request_key)
VALUES
  (@member_id, '대리 키의 책임', '대표 식별자와 업무 키를 분리합니다.', 'PUBLISHED', 50,
   '2026-07-13 13:00:00.000000', '2026-07-13 13:00:00.000000', 'post-key-001'),
  (@member_id, '업무 키의 유일성', '이메일 중복은 별도 제약으로 막습니다.', 'PUBLISHED', 40,
   '2026-07-13 14:10:00.000000', '2026-07-13 14:10:00.000000', 'post-key-002');

UPDATE members
SET email = '[email protected]'
WHERE id = @member_id;

SELECT
  l.id AS member_id,
  l.email,
  s.id AS post_id,
  s.title
FROM members AS l
JOIN posts AS s
  ON s.author_id = l.id
WHERE l.id = @member_id
ORDER BY s.id;

member_id | email           | post_id | title
1         | [email protected] | 1       | 대리 키의 책임
1         | [email protected] | 2       | 업무 키의 유일성

테이블을 새로 만들었으므로 회원과 게시글의 id는 1부터 시작합니다. 회원의 이메일은 [email protected]로 바뀌었지만 두 게시글의 author_id는 그대로이고, 변경은 업무 속성의 한 행에서 끝나 게시글의 소유 관계가 흔들리지 않았습니다. 같은 이메일을 다시 넣으면 대체 키가 막습니다.

INSERT INTO members (email, password_hash, name)
VALUES ('[email protected]', '$2a$12$duplicate.hash', '중복 사용자');

ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'members.uq_members_email'

이 오류는 설계한 대로입니다. members.id는 참조의 안정성을, uq_members_email은 이메일의 유일성을 각각 지킵니다.

외부 로그인 식별자

한 사용자가 GitHub와 Google의 계정을 모두 이을 수 있다고 해 봅시다. 공급자가 준 식별자를 members의 기본 키로 쓰면 공급자의 정책과 내부 회원의 수명이 묶입니다. 외부 계정을 따로 자식 테이블로 두면, 내부 회원 하나에 여러 계정을 이을 수 있습니다. 소셜 로그인의 흐름과 (provider, subject)로 계정을 잇는 정책은 NestJS 교재의 OAuth 2.0과 OIDC 소셜 로그인에서 다룹니다.

CREATE TABLE member_external_identities (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  member_id BIGINT UNSIGNED NOT NULL,
  provider VARCHAR(30) NOT NULL,
  provider_subject VARCHAR(191) NOT NULL,
  linked_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uk_member_external_identities_provider_subject
    (provider, provider_subject),
  UNIQUE KEY uk_member_external_identities_member_provider
    (member_id, provider),
  KEY ix_member_external_identities_member (member_id),
  CONSTRAINT ck_member_external_identities_provider
    CHECK (provider IN ('github', 'google')),
  CONSTRAINT fk_member_external_identities_member
    FOREIGN KEY (member_id)
    REFERENCES members (id)
) ENGINE = InnoDB;

INSERT INTO member_external_identities
  (member_id, provider, provider_subject)
VALUES
  (@member_id, 'github', 'github-user-9182'),
  (@member_id, 'google', 'google-user-4410');

업무 키의 유일성에는 문자열을 어떤 규칙으로 비교하는지도 들어갑니다. board_lab의 utf8mb4_0900_ai_ci를 물려받으면 대소문자의 차이를 구별하지 않는데, 사람의 이메일 정책과 외부에서 발급한 식별자의 규칙이 같다고 가정하면 안 됩니다. 예를 들어 OpenID Connect의 sub는 발급자 안에서 유일하고 대소문자를 구별하는 문자열이며, 최대 255자의 ASCII입니다.

그래서 아래 변경은 연습 모델의 외부 식별자를 대소문자와 악센트까지 구별하는 비교 규칙으로 저장합니다. MySQL의 utf8mb4_0900_bin은 utf8mb4의 인코딩 바이트를 그대로 비교에 쓰며, 끝의 공백을 다른 글자처럼 비교하는 NO PAD 규칙은 원래의 utf8mb4_0900_ai_ci와 같습니다. 이 모델에서 provider는 미리 설정한 발급자 하나를 가리킨다고 가정하며, 공급자별 실제 식별자의 길이와 발급자의 대응은 연동할 때 확인합니다. 지금의 문자열 예는 인증 자체를 구현한 코드가 아닙니다.

ALTER TABLE member_external_identities
  MODIFY provider_subject VARCHAR(255)
    CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NOT NULL;

다음 도표는 회원 두 명이 있고 한 회원에게 GitHub 계정 하나만 이어진 상태에서, 어떤 연결이 두 UNIQUE에 걸리는지 비교합니다. "외부 계정의 조합"은 (provider, provider_subject)이고, "회원과 공급자의 조합"은 (member_id, provider)입니다.

나중에 "GitHub 계정을 여러 개 이을 수 있다"로 요구가 바뀌면 두 번째 제약만 다시 검토하면 되고, 대표 식별자는 그대로입니다.

INSERT INTO member_external_identities
  (member_id, provider, provider_subject)
VALUES
  (@member_id, 'github', 'github-user-9182');

이 문장은 두 UNIQUE 가운데 먼저 검사된 제약에서 오류 1062를 냅니다. 어느 제약에서 오류가 나든 "같은 계정을 두 번 잇지 않는다"는 결과는 같지만, 두 제약이 나타내는 업무의 문장이 다르므로 이름을 나누어 두었습니다.

대리 키를 고르는 기준

AUTO_INCREMENT는 짧고 정렬에 유리하며 사람이 로그에서 읽기 쉬워, id를 발급하는 쓰기 주체가 하나인 서비스에 좋은 기본값입니다. 다만 여러 지역에서 오프라인으로 id를 발급하거나, 데이터를 합칠 때 충돌하지 않는 값이 필요하다면 UUID나 별도의 분산 id 생성 방식을 검토합니다.

이때 저장용 기본 키와 공개 식별자, 업무 키를 구별합니다. 저장용 기본 키는 조인과 외래 키에 알맞은 안정적인 값이고, 공개 식별자는 URL이나 API에 노출할 값으로 추측할 수 있는지의 규칙까지 포함하며, 업무 키는 이메일이나 외부 계정처럼 겹치면 안 되는 값입니다. AUTO_INCREMENT의 값을 감춘다고 보안이 생기지 않고, 드러낸다고 권한 검사를 생략할 수도 없습니다. 공개 id가 순차적이라는 것과 "이 사용자가 그 행을 읽을 권한이 있는가"는 별개의 문제입니다.

InnoDB에서는 보조 인덱스의 레코드마다 기본 키의 값이 함께 저장됩니다(7장 1편). 그래서 아주 긴 문자열이나 넓은 복합 키를 기본 키로 고르면 보조 인덱스도 함께 커지며, 이것이 작은 숫자 대리 키가 유리한 이유 가운데 하나입니다. 그렇다고 자연 키의 조회가 무조건 느린 것은 아니어서, UNIQUE (email)의 인덱스가 있으면 이메일 검색도 인덱스를 쓰며, 실제 선택은 EXPLAIN ANALYZE와 데이터의 분포로 확인합니다.

더 깊이: UUID를 기본 키로 쓸 때 InnoDB에서 생기는 일

InnoDB는 클러스터형 인덱스에 새 레코드를 넣을 때 페이지의 1/16을 비워 두려 하므로, 키를 오름차순이나 내림차순으로 넣으면 페이지가 15/16 정도 찹니다. 반면 키를 무작위 순서로 넣으면 페이지는 1/2에서 15/16 사이로 찹니다(InnoDB 인덱스의 물리 구조). AUTO_INCREMENT의 새 행은 인덱스의 끝쪽에 이어 붙지만, 무작위로 만든 UUID는 이미 찬 페이지들 사이에 끼어들어야 하므로 같은 행 수에도 페이지가 더 많이 필요해질 수 있습니다.

저장하는 형식도 따져야 합니다. MySQL의 UUID()는 aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee 모양의 36자 문자열을 돌려주는데, 이를 그대로 문자열 열에 두면 기본 키가 길어지고 그 값이 모든 보조 인덱스에 함께 저장됩니다. UUID_TO_BIN()은 문자열 UUID를 16바이트의 VARBINARY(16)으로 바꾸고, BIN_TO_UUID()가 그 값을 다시 문자열로 되돌립니다.

UUID()가 만드는 값은 RFC 4122의 버전 1 UUID여서, 앞의 세 묶음이 타임스탬프의 낮은 부분, 중간 부분, 높은 부분입니다. 가장 빨리 바뀌는 낮은 부분이 맨 앞에 오므로, 시간 순서로 만든 값이라도 정렬하면 흩어집니다. UUID_TO_BIN(uuid, 1)은 첫째 묶음과 셋째 묶음을 맞바꿔 빨리 바뀌는 부분을 오른쪽으로 옮기므로 인덱스의 효율을 높일 수 있지만, 버전 1 형식이 아닌 UUID에는 효과가 없고 되돌릴 때도 BIN_TO_UUID(값, 1)처럼 같은 플래그를 써야 합니다. 또 매뉴얼은 UUID()의 값이 유일하도록 만들어졌을 뿐 추측할 수 없다는 보장은 없다고 경고하므로, 공개 식별자에 예측할 수 없는 값이 필요하다면 다른 방법으로 만듭니다.

정리

• 자연 키는 업무의 뜻과 유일성을 가지지만, 그 값이 바뀌는 규칙까지 함께 따라옵니다.
• 대리 키는 안정적인 참조점을 만들지만, 업무상의 중복을 저절로 막지는 않습니다.
• 회원 게시판은 members.id를 기본 키로, 이메일을 UNIQUE NOT NULL의 대체 키로 씁니다.
• 외부 로그인 계정은 별도의 테이블과 복합 UNIQUE로 내부 회원의 수명에서 떼어 내고, 식별자의 비교 규칙도 발급자의 정의에 맞춥니다.
• MySQL의 AUTO_INCREMENT는 구현의 방식이며, 대리 키 자체는 제품과 무관한 모델링의 선택입니다.

연습 문제

1. 첨부파일의 URL

회원 게시판에 첨부파일의 출처를 저장하는 attachments를 더하려 합니다. 첨부파일에는 URL이 있지만, 운영자의 URL 정규화나 도메인의 이전으로 URL이 바뀔 수 있고, 같은 정규화 URL은 두 번 저장하지 않아야 합니다. URL을 기본 키로 쓰지 않는 이유를 설명하고, 대리 키와 업무 키를 나눈 DDL과, URL이 바뀌어도 기존의 참조가 유지되는지 확인하는 SQL을 써 보세요.

DROP TABLE IF EXISTS attachment_reference_demo;
DROP TABLE IF EXISTS attachments;

CREATE TABLE 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;

INSERT INTO attachments (canonical_url, title)
VALUES ('https://dev.mysql.com/doc/refman/8.4/en/', 'MySQL 8.4 Reference Manual');

SET @attachment_id := LAST_INSERT_ID();

UPDATE attachments
SET canonical_url = 'https://dev.mysql.com/doc/refman/8.4/en/index.html'
WHERE id = @attachment_id;

SELECT id AS attachment_id, canonical_url
FROM attachments
WHERE id = @attachment_id;

attachment_id | canonical_url
1             | https://dev.mysql.com/doc/refman/8.4/en/index.html

CREATE TABLE attachment_reference_demo (
  reference_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  attachment_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (reference_id),
  CONSTRAINT fk_attachment_reference_demo_file
    FOREIGN KEY (attachment_id) REFERENCES attachments (id)
) ENGINE = InnoDB;

INSERT INTO attachment_reference_demo (attachment_id)
VALUES (@attachment_id);

UPDATE attachments
SET canonical_url = 'https://dev.mysql.com/doc/refman/8.4/en/'
WHERE id = @attachment_id;

SELECT r.reference_id, r.attachment_id, a.canonical_url
FROM attachment_reference_demo AS r
JOIN attachments AS a ON a.id = r.attachment_id;

reference_id | attachment_id | canonical_url
1            | 1             | https://dev.mysql.com/doc/refman/8.4/en/

2. 대소문자만 다른 외부 식별자

본문의 비교 규칙 보완 전과 후에, GitHub 계정이 아직 이어지지 않은 서로 다른 두 회원에게 ('github', 'User-1')과 ('github', 'user-1')을 차례로 이으면 각각 어떻게 되나요?

다음 글 복합 키와 일대다 관계에서는 열 하나로 끝나지 않는 복합 키를 다루고, 회원과 카테고리, 게시글 사이의 일대다 관계를 완성합니다.