단일 테이블 상속

DROP TABLE IF EXISTS posts_single_bad;
CREATE TABLE posts_single_bad (
  post_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  post_type VARCHAR(16) NOT NULL,
  title VARCHAR(160) NOT NULL,
  body_chars INT UNSIGNED NULL,
  image_width_px INT UNSIGNED NULL,
  link_kind VARCHAR(16) NULL,
  CHECK (post_type IN ('TEXT', 'IMAGE', 'LINK'))
) ENGINE=InnoDB;

INSERT INTO posts_single_bad
  (post_type, title, body_chars, image_width_px, link_kind)
VALUES
  ('IMAGE', '너비 없는 이미지', NULL, NULL, 'DOWNLOAD'),
  ('TEXT', '필드 없는 글', NULL, NULL, NULL),
  ('LINK', '이미지 너비가 있는 링크', NULL, 3600, 'EXTERNAL');

SELECT * FROM posts_single_bad ORDER BY post_id;

post_id | post_type | title                   | body_chars | image_width_px | link_kind
1       | IMAGE     | 너비 없는 이미지        | NULL       | NULL           | DOWNLOAD
2       | TEXT      | 필드 없는 글            | NULL       | NULL           | NULL
3       | LINK      | 이미지 너비가 있는 링크 | NULL       | 3600           | EXTERNAL

DROP TRIGGER IF EXISTS prevent_post_type_change;
DROP TABLE IF EXISTS member_post_single;
DROP TABLE IF EXISTS posts_single;

CREATE TABLE posts_single (
  post_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  post_type VARCHAR(16) NOT NULL,
  title VARCHAR(160) NOT NULL,
  canonical_url VARCHAR(700) NOT NULL,
  body_chars INT UNSIGNED NULL,
  alt_text VARCHAR(255) NULL,
  image_width_px INT UNSIGNED NULL,
  link_kind VARCHAR(16) NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  CONSTRAINT uq_post_single_url UNIQUE (canonical_url),
  CONSTRAINT chk_post_single_type CHECK (post_type IN ('TEXT', 'IMAGE', 'LINK')),
  CONSTRAINT chk_post_single_payload CHECK (
    (post_type = 'TEXT' AND body_chars IS NOT NULL
     AND body_chars BETWEEN 1 AND 1000000
     AND alt_text IS NULL AND image_width_px IS NULL AND link_kind IS NULL)
    OR
    (post_type = 'IMAGE' AND body_chars IS NULL AND alt_text IS NOT NULL
     AND image_width_px IS NOT NULL AND image_width_px BETWEEN 1 AND 20000
     AND link_kind IS NULL)
    OR
    (post_type = 'LINK' AND body_chars IS NULL AND alt_text IS NULL
     AND image_width_px IS NULL AND link_kind IS NOT NULL
     AND link_kind IN ('EXTERNAL', 'INTERNAL', 'DOWNLOAD'))
  )
) ENGINE=InnoDB;

CREATE TABLE member_post_single (
  member_id BIGINT UNSIGNED NOT NULL,
  post_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (member_id, post_id),
  FOREIGN KEY (member_id) REFERENCES members (id) ON DELETE CASCADE,
  FOREIGN KEY (post_id) REFERENCES posts_single (post_id) ON DELETE RESTRICT
) ENGINE=InnoDB;

DELIMITER //
CREATE TRIGGER prevent_post_type_change BEFORE UPDATE ON posts_single
FOR EACH ROW BEGIN
  IF NEW.post_type <> OLD.post_type THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'post type is immutable';
  END IF;
END//
DELIMITER ;

SET @member_id = (SELECT id FROM members ORDER BY id LIMIT 1);
INSERT INTO posts_single
  (post_type, title, canonical_url, body_chars)
VALUES ('TEXT', 'B+Tree 글', 'https://example.com/a/btree', 30);
SET @single_text = LAST_INSERT_ID();
INSERT INTO posts_single
  (post_type, title, canonical_url, alt_text, image_width_px)
VALUES ('IMAGE', '인덱스 구조 이미지', 'https://example.com/image/index',
        'B+Tree 구조', 1800);
INSERT INTO posts_single
  (post_type, title, canonical_url, link_kind)
VALUES ('LINK', '실행 계획 실습', 'https://example.com/l/explain', 'INTERNAL');
INSERT INTO member_post_single VALUES (@member_id, @single_text);

SELECT post_type, COUNT(*) AS posts
FROM posts_single
GROUP BY post_type
ORDER BY post_type;

post_type | posts
IMAGE     | 1
LINK      | 1
TEXT      | 1

SELECT post_id, post_type FROM posts_single
WHERE NOT (
  (post_type = 'TEXT' AND body_chars IS NOT NULL
   AND body_chars BETWEEN 1 AND 1000000
   AND alt_text IS NULL AND image_width_px IS NULL AND link_kind IS NULL)
  OR
  (post_type = 'IMAGE' AND body_chars IS NULL AND alt_text IS NOT NULL
   AND image_width_px IS NOT NULL AND image_width_px BETWEEN 1 AND 20000
   AND link_kind IS NULL)
  OR
  (post_type = 'LINK' AND body_chars IS NULL AND alt_text IS NULL
   AND image_width_px IS NULL AND link_kind IS NOT NULL
   AND link_kind IN ('EXTERNAL', 'INTERNAL', 'DOWNLOAD'))
);

INSERT INTO posts_single
  (post_type, title, canonical_url, body_chars, link_kind)
VALUES ('TEXT', '오염 예제', 'https://example.com/bad', 10, 'DOWNLOAD');

UPDATE posts_single
SET post_type = 'IMAGE', body_chars = NULL,
    alt_text = '바꾼 이미지', image_width_px = 600
WHERE post_id = @single_text;

SELECT l.member_id, l.post_id FROM member_post_single AS l
LEFT JOIN posts_single AS r ON r.post_id = l.post_id
WHERE r.post_id IS NULL;

ERROR 3819 (HY000): Check constraint 'chk_post_single_payload' is violated.
ERROR 1644 (45000): post type is immutable