복합 식별 키와 계층 구조

DROP TABLE IF EXISTS reply_reaction_identifying;
DROP TABLE IF EXISTS comment_reply_identifying;

CREATE TABLE comment_reply_identifying (
  post_id BIGINT UNSIGNED NOT NULL,
  sequence_no SMALLINT UNSIGNED NOT NULL,
  reply_no INT UNSIGNED NOT NULL,
  body VARCHAR(500) NOT NULL,
  PRIMARY KEY (post_id, sequence_no, reply_no),
  FOREIGN KEY (post_id, sequence_no)
    REFERENCES post_comment_identifying (post_id, sequence_no)
);
CREATE TABLE reply_reaction_identifying (
  post_id BIGINT UNSIGNED NOT NULL,
  sequence_no SMALLINT UNSIGNED NOT NULL,
  reply_no INT UNSIGNED NOT NULL,
  reaction_no INT UNSIGNED NOT NULL,
  emoji VARCHAR(16) NOT NULL,
  PRIMARY KEY (post_id, sequence_no, reply_no, reaction_no),
  FOREIGN KEY (post_id, sequence_no, reply_no)
    REFERENCES comment_reply_identifying
      (post_id, sequence_no, reply_no)
);

SET @identifying_post_id := (
  SELECT MIN(post_id) FROM post_comment_identifying
);

INSERT INTO comment_reply_identifying
  (post_id, sequence_no, reply_no, body)
VALUES (@identifying_post_id, 1, 1, '복합 키 답글');

INSERT INTO reply_reaction_identifying
  (post_id, sequence_no, reply_no, reaction_no, emoji)
VALUES (@identifying_post_id, 1, 1, 1, '좋아요');

UPDATE post_comment_identifying
SET sequence_no = 2
WHERE post_id = @identifying_post_id
  AND sequence_no = 1;
-- ERROR 1451: comment_reply_identifying이 기존 복합 키를 참조

comment PK columns: 2
reply PK columns: 3
reaction PK columns: 4

move comment sequence 1 → 2 with RESTRICT: ERROR 1451
with ON UPDATE CASCADE: comment + all replies + all reactions change
simple reaction URL id: unavailable

DROP TABLE IF EXISTS reply_reaction;
DROP TABLE IF EXISTS comment_reply;

CREATE TABLE comment_reply (
  reply_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  comment_id BIGINT UNSIGNED NOT NULL,
  reply_no INT UNSIGNED NOT NULL,
  body VARCHAR(500) NOT NULL,
  PRIMARY KEY (reply_id),
  UNIQUE (comment_id, reply_no),
  FOREIGN KEY (comment_id)
    REFERENCES post_comment_surrogate (comment_id)
);
CREATE TABLE reply_reaction (
  reaction_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  reply_id BIGINT UNSIGNED NOT NULL,
  member_id BIGINT UNSIGNED NOT NULL,
  emoji VARCHAR(16) NOT NULL,
  PRIMARY KEY (reaction_id),
  UNIQUE (reply_id, member_id, emoji),
  FOREIGN KEY (reply_id) REFERENCES comment_reply (reply_id),
  FOREIGN KEY (member_id) REFERENCES members (id)
);

SET @surrogate_parent_comment_id := (
  SELECT MIN(comment_id) FROM post_comment_surrogate
);
SET @reaction_member_id := (SELECT MIN(id) FROM members);

INSERT INTO comment_reply (comment_id, reply_no, body)
VALUES (@surrogate_parent_comment_id, 1, '대리 키 답글');

SET @reply_id := LAST_INSERT_ID();

INSERT INTO reply_reaction (reply_id, member_id, emoji)
VALUES (@reply_id, @reaction_member_id, '좋아요');

INSERT INTO comment_reply (comment_id, reply_no, body)
VALUES (@surrogate_parent_comment_id, 1, '중복 답글');
-- ERROR 1062: (comment_id, reply_no) UNIQUE 중복

INSERT INTO reply_reaction (reply_id, member_id, emoji)
VALUES (@reply_id, @reaction_member_id, '좋아요');
-- ERROR 1062: (reply_id, member_id, emoji) UNIQUE 중복

comment reference: comment_id
reply reference: reply_id
reaction reference: reaction_id
duplicate reply_no in comment: ERROR 1062
duplicate same member emoji on reply: ERROR 1062

SELECT table_name, constraint_name, COUNT(*) AS column_count
FROM information_schema.key_column_usage
WHERE table_schema = DATABASE()
  AND table_name IN (
    'comment_reply_identifying', 'reply_reaction_identifying',
    'comment_reply', 'reply_reaction'
  )
GROUP BY table_name, constraint_name
ORDER BY table_name, constraint_name;

SELECT table_name, index_length
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_name LIKE '%reaction%';