단일 테이블 상속

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;

rows inserted=3
valid IMAGE payloads=0
valid TEXT payloads=0
valid LINK payloads=0
constraint errors=0

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 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 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 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);

posts=3
TEXT=1, IMAGE=1, LINK=1
member links=1
invalid payload insert=ERROR 3819
type mutation=SIGNAL 45000

SELECT post_id,post_type FROM posts_single
WHERE NOT(
 (post_type='TEXT' 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 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 IN('EXTERNAL','INTERNAL','DOWNLOAD'))
);

INSERT INTO posts_single
 (post_type,title,canonical_url,body_chars,link_kind)
VALUES('TEXT','오염 fixture','https://example.com/bad',10,'DOWNLOAD');
-- ERROR 3819: chk_post_single_payload

UPDATE posts_single SET post_type='IMAGE',body_chars=NULL,
 alt_text='ExampleTV',image_width_px=600 WHERE post_id=@single_text;
-- ERROR 1644: post type is immutable

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