회원 게시판 논리 모델

DROP TABLE IF EXISTS reply_key_explosion_bad;

INSERT INTO members (email, password_hash, name, status)
VALUES ('old@example.com', '$2b$12$logical.key.fixture',
        '논리 키 실습 회원', 'ACTIVE');

SET @logical_member_id := LAST_INSERT_ID();

CREATE TABLE reply_key_explosion_bad (
  member_email VARCHAR(255) NOT NULL,
  post_created_at DATETIME(6) NOT NULL,
  comment_sequence SMALLINT UNSIGNED NOT NULL,
  reply_no INT UNSIGNED NOT NULL,
  body VARCHAR(500) NOT NULL,
  PRIMARY KEY (
    member_email, post_created_at,
    comment_sequence, reply_no
  )
);

INSERT INTO reply_key_explosion_bad
  (member_email, post_created_at, comment_sequence, reply_no, body)
VALUES
  ('old@example.com', '2026-07-14 09:00:00', 1, 1,
   '자연 키를 복사한 답글');

UPDATE members
SET email = 'new@example.com'
WHERE id = @logical_member_id;
-- 별도 reply 자연 키에는 이전 email이 남는다.

member current email: new@example.com
reply key email: old@example.com
declared FK between them: none
PK width: long string + datetime + 2 numbers
identity updates required: entire descendant tree

DROP TABLE IF EXISTS logical_reply;
DROP TABLE IF EXISTS logical_comment;

CREATE TABLE logical_comment (
  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)
);
CREATE TABLE logical_reply (
  reply_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  comment_id BIGINT UNSIGNED NOT NULL,
  author_id BIGINT UNSIGNED NOT NULL,
  body VARCHAR(500) NOT NULL,
  PRIMARY KEY (reply_id),
  FOREIGN KEY (comment_id) REFERENCES logical_comment (comment_id),
  FOREIGN KEY (author_id) REFERENCES members (id)
);
-- member_profiles shared PK와 post_tags pair PK는 ch11-3 정의를 유지한다.

SET @logical_post_id := (SELECT MIN(id) FROM posts);

INSERT INTO logical_comment (post_id, sequence_no, summary)
VALUES (@logical_post_id, 1, '논리 모델 댓글');

SET @logical_comment_id := LAST_INSERT_ID();

INSERT INTO logical_reply (comment_id, author_id, body)
VALUES (@logical_comment_id, @logical_member_id, '안정적인 대리 키 답글');

UPDATE members
SET email = 'stable-id@example.com'
WHERE id = @logical_member_id;

UPDATE logical_comment
SET sequence_no = 2
WHERE comment_id = @logical_comment_id;

INSERT INTO logical_comment (post_id, sequence_no, summary)
VALUES (@logical_post_id, 2, '중복 순번');
-- ERROR 1062: (post_id, sequence_no) UNIQUE 중복

INSERT INTO logical_reply (comment_id, author_id, body)
VALUES (999999, @logical_member_id, '고아 답글');
-- ERROR 1452: logical_reply.comment_id FK

email UPDATE: member row 1 affected, descendant key updates 0
comment sequence UPDATE: comment row 1 affected, reply FK updates 0
duplicate post+sequence: ERROR 1062
orphan reply: ERROR 1452
profile duplicate / post-tag duplicate: ERROR 1062

SELECT c.reply_id
FROM logical_reply AS c
LEFT JOIN logical_comment AS p ON p.comment_id = c.comment_id
WHERE p.comment_id IS NULL;

SELECT post_id, sequence_no, COUNT(*)
FROM logical_comment
GROUP BY post_id, sequence_no HAVING COUNT(*) > 1;

SELECT member_id, COUNT(*)
FROM member_profiles
GROUP BY member_id HAVING COUNT(*) > 1;

SELECT post_id, tag_id, COUNT(*)
FROM post_tags
GROUP BY post_id, tag_id HAVING COUNT(*) > 1;