식별 관계와 비식별 관계

ERD의 실선과 점선의 모양보다 중요한 것은, 자식을 어떤 값으로 참조하고 부모의 이동과 삭제를 어떻게 볼지입니다. 10장에서는 후보 키로 행을 구별하고 외래 키로 테이블을 이었습니다. 이 장은 두 개념을 합쳐, 자식의 정체성을 부모의 키와 함께 말해야 하는지를 결정합니다.

부모의 키가 자식의 기본 키에 들어가면 식별 관계(identifying relationship), 자식이 따로 기본 키를 두고 부모의 키를 일반 외래 키로만 가지면 비식별 관계(non-identifying relationship)입니다. 이 글의 예는 게시글 하나에 순서가 있는 댓글이 여러 개 있고 sequence_no가 게시글 안에서만 유일한 경우이며, 답글이 아직 없어 댓글을 바깥에서 따로 참조할 요구도 작습니다. (post_id, sequence_no)의 복합 기본 키와, comment_id의 대리 기본 키에 조합의 UNIQUE를 더한 두 선택을 비교합니다.

부모의 범위가 없는 순번 키

이 글은 MySQL 8.4에서 10장부터 쌓은 board_lab의 연결을 그대로 쓰며, @@default_storage_engine이 InnoDB이고 @@foreign_key_checks가 1이라고 전제합니다. 아래의 CREATE TABLE은 엔진을 생략했으므로, 기본 엔진이 다르면 같은 참조 동작이 보장되지 않습니다. 또 posts에 서로 다른 게시글이 두 개 이상 있어야 합니다.

첫 게시글에 1번 댓글을 넣은 뒤 두 번째 게시글에도 1번 댓글을 넣는 것은 정상적인 업무인데, 순번을 전역 기본 키로 두면 중복으로 거부됩니다.

DROP TABLE IF EXISTS comment_bad;

SET @post_one := (SELECT MIN(id) FROM posts);
SET @post_two := (SELECT MIN(id) FROM posts WHERE id > @post_one);

CREATE TABLE comment_bad (
  sequence_no SMALLINT UNSIGNED NOT NULL,
  post_id BIGINT UNSIGNED NOT NULL,
  summary VARCHAR(240) NOT NULL,
  PRIMARY KEY (sequence_no),
  FOREIGN KEY (post_id) REFERENCES posts (id)
);
INSERT INTO comment_bad VALUES (1, @post_one, '인덱스 원리');
INSERT INTO comment_bad VALUES (1, @post_two, '트랜잭션 원자성');

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

첫 INSERT는 성공하지만, 서로 다른 게시글의 정당한 1번 댓글인 두 번째 INSERT는 기본 키가 부모의 범위를 모르기 때문에 거부됩니다. 업무상으로는 중복이 아닌데 스키마상으로는 중복인 셈이며, sequence_no의 유일 범위가 게시글이라는 요구를 키가 나타내지 못한 것입니다.

sequence_no는 부분 식별자(partial identifier)여서, 부모의 post_id와 함께 보아야 댓글을 많아야 하나로 좁힐 수 있습니다. 외래 키를 별도의 열로 두었다고 기본 키의 유일 범위가 저절로 넓어지지는 않습니다. 전역의 comment_id를 더하더라도 (post_id, sequence_no)라는 업무의 유일성을 버리면, 같은 게시글의 1번을 두 번 저장할 수 있게 되므로 대표 id와 업무 후보 키를 함께 지킵니다.

부분 키의 유일 범위는 반례로 찾습니다. 같은 순서가 어디까지 되풀이될 수 있는지 문장으로 적고, post_id와 sequence_no 가운데 하나를 뺐을 때 유일성이 깨지는지 봅니다. 다른 테이블이 댓글을 얼마나 자주 참조할지, 댓글이 다른 게시글로 옮겨질 수 있는 업무인지도 확인합니다.

식별 관계의 뜻

복합 기본 키 (post_id, sequence_no)는 "이 게시글의 몇 번째 댓글"이라는 정체성을 직접 나타냅니다. 부모 없이는 자식의 키를 말할 수 없고, 다른 게시글로 옮기면 기본 키 자체가 바뀝니다. 비식별의 방식은 comment_id로 대표 식별을 고정하고, post_id를 외래 키로, 조합을 UNIQUE로 둡니다. 참조와 이동은 단순해지지만, 업무의 유일성을 별도의 제약으로 관리해야 합니다.

요즘의 기본값은 대리 키인 경우가 많지만, 부모에 완전히 종속된 자식에는 복합 키도 충분히 합리적입니다. 대표 키를 고를 때는 부모를 지우면 자식의 뜻도 함께 사라지는지, 아래의 테이블이 복합 외래 키를 물려받는 비용이 얼마인지, 부모를 바꾸는 일이 정체성의 변경인지 일반 속성의 변경인지, 부모 기본 키의 타입과 깊이가 인덱스와 JOIN에 얼마나 번지는지를 봅니다.

두 모델을 비교한다

단순한 댓글을 복합 기본 키의 식별 관계로 먼저 구현하고, 따로 참조하는 답글이나 공유가 늘어날 경우를 위한 대리 키의 방식도 함께 만듭니다. 두 테이블은 게시글 id, 순번, 요약을 함께 갖지만 키가 다릅니다. post_comment_identifying은 PRIMARY KEY (post_id, sequence_no)로 행을 구별하고, post_comment_surrogate는 AUTO_INCREMENT인 comment_id를 기본 키로 두고 조합을 UNIQUE로 지킵니다. 대리 모델의 첫 행을 넣은 직후 LAST_INSERT_ID()로 받아 둔 @surrogate_comment_id는 뒤에서 comment_id로 한 행을 찾을 때 씁니다.

DROP TABLE IF EXISTS post_comment_surrogate;
DROP TABLE IF EXISTS post_comment_identifying;

CREATE TABLE post_comment_identifying (
  post_id BIGINT UNSIGNED NOT NULL,
  sequence_no SMALLINT UNSIGNED NOT NULL,
  summary VARCHAR(240) NOT NULL,
  checked_at DATETIME(6) NULL,
  PRIMARY KEY (post_id, sequence_no),
  FOREIGN KEY (post_id) REFERENCES posts (id)
    ON DELETE CASCADE,
  CHECK (sequence_no >= 1)
);

CREATE TABLE post_comment_surrogate (
  comment_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  post_id BIGINT UNSIGNED NOT NULL,
  sequence_no SMALLINT UNSIGNED NOT NULL,
  summary VARCHAR(240) NOT NULL,
  PRIMARY KEY (comment_id),
  UNIQUE (post_id, sequence_no),
  FOREIGN KEY (post_id) REFERENCES posts (id)
);

INSERT INTO post_comment_identifying
  (post_id, sequence_no, summary)
VALUES
  (@post_one, 1, '인덱스 원리'),
  (@post_two, 1, '트랜잭션 원자성');

INSERT INTO post_comment_surrogate
  (post_id, sequence_no, summary)
VALUES (@post_one, 1, '인덱스 원리');

SET @surrogate_comment_id := LAST_INSERT_ID();

INSERT INTO post_comment_surrogate
  (post_id, sequence_no, summary)
VALUES (@post_two, 1, '트랜잭션 원자성');

두 모델 모두 서로 다른 게시글의 1번 댓글은 받아들이고, 같은 게시글의 같은 순번은 거부합니다.

INSERT INTO post_comment_identifying
  (post_id, sequence_no, summary)
VALUES (@post_one, 1, '중복 댓글');

INSERT INTO post_comment_surrogate
  (post_id, sequence_no, summary)
VALUES (@post_one, 1, '중복 댓글');

ERROR 1062 (23000): Duplicate entry '1-1' for key 'post_comment_identifying.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1-1' for key 'post_comment_surrogate.post_id'

식별 모델에서는 기본 키가, 대리 모델에서는 이름을 붙이지 않아 첫 열의 이름 post_id로 불리는 UNIQUE가 중복을 막았습니다. 다른 테이블이 댓글을 참조할 때 쓰는 키는 식별 모델에서 (post_id, sequence_no)의 두 열, 대리 모델에서 comment_id 한 열입니다.

위의 두 방식은 대표 키뿐 아니라 삭제의 규칙과 CHECK도 다릅니다. 식별 기본 키를 고른다고 CASCADE가 저절로 붙는 것이 아니고, 대리 기본 키를 고른다고 0번 순번을 반드시 허용해야 하는 것도 아닙니다. 같은 업무 규칙에서 키의 방식만 비교하려면 다른 조건을 맞추어야 하므로, "순번은 1부터"를 대리 모델에도 적용합니다.

ALTER TABLE post_comment_surrogate
  ADD CONSTRAINT ck_post_comment_surrogate_sequence
    CHECK (sequence_no >= 1);

이제 두 방식 모두 0을 거부합니다. 0의 거부는 CHECK가, 같은 게시글 순번의 중복 거부는 기본 키나 UNIQUE가 맡습니다.

CASCADE는 댓글이 게시글에 완전히 종속되고 삭제의 감사 요구가 없을 때만 맞습니다. 댓글의 기록을 남겨야 한다면 게시글을 소프트 삭제하고 RESTRICT를 고릅니다. 부모의 삭제를 실험할 때는 그 부모를 참조하는 모든 외래 키를 확인해야 합니다. 지금의 posts에서 첫 게시글을 지우면 post_comment_identifying에 CASCADE가 있어도, comment_bad와 post_comment_surrogate, 그리고 10장에서 만든 post_tags 같은 다른 테이블의 외래 키가 기본 동작인 NO ACTION(InnoDB에서는 RESTRICT와 같습니다)으로 삭제를 거부합니다. 외래 키 하나의 규칙과 DELETE 전체의 성공을 같은 것으로 읽지 말고, 규칙을 따로 보려면 다른 참조가 없는 실습용 부모에서 한 모델씩 비교하며, 트랜잭션 안에서 결과를 읽은 뒤 ROLLBACK합니다.

대리 키의 방식에서도 조합의 UNIQUE는 조회의 경로와 업무의 검증에 쓸모가 있습니다. UNIQUE의 열 순서는 게시글별 순번의 조회와 맞지만, comment_id만으로 찾는 조회를 대신하지는 않습니다.

키의 최소성과 참조의 폭

두 모델에서 대표적인 조회를 실행하고, 인덱스의 정의를 비교합니다. 식별 모델은 업무에서 쓰는 두 열 (post_id, sequence_no)로, 대리 모델은 앞에서 받아 둔 comment_id 하나로 같은 댓글을 찾습니다. 아래 결과에는 두 SELECT의 행만 옮겼고, 열이 많은 SHOW INDEX의 결과는 그다음 문단에서 필요한 열만 골라 설명합니다.

SELECT post_id, sequence_no, summary
FROM post_comment_identifying
WHERE post_id = @post_one AND sequence_no = 1;

SELECT comment_id, post_id, sequence_no, summary
FROM post_comment_surrogate
WHERE comment_id = @surrogate_comment_id;

SHOW INDEX FROM post_comment_identifying;
SHOW INDEX FROM post_comment_surrogate;

post_id | sequence_no | summary
1       | 1           | 인덱스 원리

comment_id | post_id | sequence_no | summary
1          | 1       | 1           | 인덱스 원리

두 조회 모두 기본 키나 UNIQUE로 한 행을 정확히 찾습니다. SHOW INDEX에서 식별 모델의 PRIMARY는 Seq_in_index가 1인 post_id와 2인 sequence_no의 두 행으로 나오고, 대리 모델은 한 열짜리 PRIMARY와 별도의 UNIQUE post_id가 나옵니다. 대리 모델에서는 외래 키 post_id가 그 UNIQUE의 왼쪽 열을 쓰므로, 외래 키를 위한 인덱스가 따로 생기지 않습니다.

식별 관계는 강한 소유를 나타내지만, 모든 소유 관계를 식별로 만들 필요는 없습니다. 게시글과 함께만 뜻이 있고 순서로 식별되는 행에는 자연스럽지만, 따로 수명을 갖거나 바깥에서 링크로 가리키게 되면 대리 키가 안정적입니다. 부모의 기본 키가 복합이면 자식의 기본 키와 그 아래의 모든 외래 키로 계속 전파되어, 깊이가 깊어질 때마다 열이 늘 수 있습니다. 이 비용은 다음 글에서 답글과 반응의 계층으로 재현합니다.

복합 기본 키를 대리 키로 이관할 때는 comment_id를 채우고, 조합의 UNIQUE를 유지하며, 아래의 외래 키를 잠시 두 방식으로 함께 쓰고, 전환이 일치하는지 확인하는 단계를 나눕니다. 대량의 CASCADE는 트랜잭션 하나의 잠금과 언두 로그를 키울 수 있으므로, 보존의 규칙과 나누어 지우는 단위를 검토합니다.

예외도 있습니다. 순서를 다시 매기는 일은 식별 기본 키의 변경이 되어 아래의 외래 키까지 ON UPDATE의 비용을 만들고, 가운데에 동시에 끼워 넣는 일은 조합 UNIQUE의 충돌과 번호를 다시 매기는 잠금이 필요합니다. 부모의 tenant_id까지 복합 키에 넣으면 보안 범위의 이득과 전파의 폭을 함께 따져야 하며, AUTO_INCREMENT 대리 키가 있어도 같은 게시글 순번의 중복은 저절로 막히지 않습니다.

ERD 선의 모양을 취향으로 고르지 말고, 자식을 말로 식별하는 방식과 바깥 참조, 이동, 삭제를 예제 데이터로 확인합니다. 대리 키를 고르는 것이 업무의 UNIQUE를 없애는 것은 아닙니다.

정리

• 식별 관계는 부모의 키가 자식의 기본 키에 들어갑니다.
• 부분 식별자는 부모의 범위와 함께 후보 키가 되며, 혼자 전역 기본 키로 두면 정당한 행을 거부합니다.
• 대리 기본 키를 쓰더라도 업무 조합의 UNIQUE는 지킵니다.
• 참조의 깊이와 이동, 삭제의 규칙이 식별 여부를 결정합니다.

연습 문제

1. 게시글 안의 질문 목록

게시글 안에서 1번부터 시작하는 질문의 목록을 설계해 보세요. 질문의 답변과 답글이 질문을 참조합니다. 식별 관계와 비식별 관계를 비교하고 하나를 고릅니다.

DROP TABLE IF EXISTS post_question;
CREATE TABLE post_question (
  question_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  post_id BIGINT UNSIGNED NOT NULL,
  sequence_no SMALLINT UNSIGNED NOT NULL,
  body VARCHAR(500) NOT NULL,
  PRIMARY KEY (question_id),
  UNIQUE (post_id, sequence_no),
  CONSTRAINT ck_post_question_sequence CHECK (sequence_no >= 1),
  FOREIGN KEY (post_id) REFERENCES posts (id)
);

2. 댓글을 다른 게시글로 옮기면

운영자가 잘못 올라간 댓글을 다른 게시글로 옮길 수 있게 하려 합니다. 식별 모델과 대리 모델에서 이 작업은 각각 어떻게 다른가요?

다음 글 복합 식별 키와 계층 구조에서는 식별 키가 답글과 반응의 단계로 전파될 때 쿼리와 인덱스가 얼마나 넓어지는지 재고, 비식별로 바꿀 기준을 세웁니다.