구체 테이블 상속

DROP TABLE IF EXISTS member_post_reference_bad;
CREATE TABLE member_post_reference_bad(
 member_id BIGINT UNSIGNED NOT NULL,
 post_type VARCHAR(16) NOT NULL,
 post_id BIGINT UNSIGNED NOT NULL,
 PRIMARY KEY(member_id,post_type,post_id),
 CHECK(post_type IN('TEXT','IMAGE','LINK')),
 CONSTRAINT fk_bad_member FOREIGN KEY(member_id)
  REFERENCES members(id) ON DELETE CASCADE
) ENGINE=InnoDB;
SET @member_id = (SELECT id FROM members ORDER BY id LIMIT 1);
INSERT INTO member_post_reference_bad VALUES
 (@member_id,'TEXT',999999),
 (@member_id,'IMAGE',888888);
SELECT * FROM member_post_reference_bad;

inserted links=2
valid member reference=yes
valid text references=0
valid image references=0
database errors=0

DROP VIEW IF EXISTS concrete_post_feed_v;
DROP TABLE IF EXISTS member_link_post;
DROP TABLE IF EXISTS member_image_post;
DROP TABLE IF EXISTS member_text_post;
DROP TABLE IF EXISTS link_posts;
DROP TABLE IF EXISTS image_posts;
DROP TABLE IF EXISTS text_posts;

CREATE TABLE text_posts(
 text_post_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(160) NOT NULL,
 canonical_url VARCHAR(700) NOT NULL,body_chars INT UNSIGNED NOT NULL,
 created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
 UNIQUE KEY uq_text_url(canonical_url),CHECK(body_chars BETWEEN 1 AND 1000000)
) ENGINE=InnoDB;
CREATE TABLE image_posts(
 image_post_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(160) NOT NULL,
 canonical_url VARCHAR(700) NOT NULL,alt_text VARCHAR(255) NOT NULL,image_width_px INT UNSIGNED NOT NULL,
 created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
 UNIQUE KEY uq_image_url(canonical_url),CHECK(image_width_px BETWEEN 1 AND 20000)
) ENGINE=InnoDB;
CREATE TABLE link_posts(
 link_post_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(160) NOT NULL,
 target_url VARCHAR(700) NOT NULL,link_kind VARCHAR(16) NOT NULL,
 created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
 UNIQUE KEY uq_link_target(target_url),CHECK(link_kind IN('EXTERNAL','INTERNAL','DOWNLOAD'))
) ENGINE=InnoDB;

CREATE TABLE member_text_post(
 member_id BIGINT UNSIGNED NOT NULL,text_post_id BIGINT UNSIGNED NOT NULL,
 PRIMARY KEY(member_id,text_post_id),
 FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE CASCADE,
 FOREIGN KEY(text_post_id) REFERENCES text_posts(text_post_id) ON DELETE RESTRICT
) ENGINE=InnoDB;
CREATE TABLE member_image_post(
 member_id BIGINT UNSIGNED NOT NULL,image_post_id BIGINT UNSIGNED NOT NULL,
 PRIMARY KEY(member_id,image_post_id),
 FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE CASCADE,
 FOREIGN KEY(image_post_id) REFERENCES image_posts(image_post_id) ON DELETE RESTRICT
) ENGINE=InnoDB;
CREATE TABLE member_link_post(
 member_id BIGINT UNSIGNED NOT NULL,link_post_id BIGINT UNSIGNED NOT NULL,
 PRIMARY KEY(member_id,link_post_id),
 FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE CASCADE,
 FOREIGN KEY(link_post_id) REFERENCES link_posts(link_post_id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE VIEW concrete_post_feed_v AS
SELECT 'TEXT' post_type,text_post_id post_id,title,canonical_url target_url,
 body_chars display_hint,created_at FROM text_posts
UNION ALL
SELECT 'IMAGE',image_post_id,title,canonical_url,
 image_width_px,created_at FROM image_posts
UNION ALL
SELECT 'LINK',link_post_id,title,target_url,NULL,created_at FROM link_posts;

INSERT INTO text_posts(title,canonical_url,body_chars)
VALUES('InnoDB 인덱스 읽기','https://example.com/text/innodb',25);
SET @text_post_id=LAST_INSERT_ID();
INSERT INTO member_text_post VALUES(@member_id,@text_post_id);

text posts=1
member text links=1
common view rows=1
orphan concrete links=0
invalid text id insert=ERROR 1452

INSERT INTO member_text_post VALUES(@member_id,999999);
-- ERROR 1452
SELECT l.member_id,l.text_post_id FROM member_text_post l
LEFT JOIN text_posts r ON r.text_post_id=l.text_post_id WHERE r.text_post_id IS NULL;
SELECT l.member_id,l.image_post_id FROM member_image_post l
LEFT JOIN image_posts r ON r.image_post_id=l.image_post_id WHERE r.image_post_id IS NULL;
SELECT l.member_id,l.link_post_id FROM member_link_post l
LEFT JOIN link_posts r ON r.link_post_id=l.link_post_id WHERE r.link_post_id IS NULL;
SELECT post_type,post_id FROM concrete_post_feed_v
GROUP BY post_type,post_id HAVING COUNT(*)>1;

SELECT 'TEXT' kind,a.text_post_id id,a.title FROM member_text_post l
JOIN text_posts a ON a.text_post_id=l.text_post_id WHERE l.member_id=@member_id
UNION ALL
SELECT 'IMAGE',v.image_post_id,v.title FROM member_image_post l
JOIN image_posts v ON v.image_post_id=l.image_post_id WHERE l.member_id=@member_id
UNION ALL
SELECT 'LINK',b.link_post_id,b.title FROM member_link_post l
JOIN link_posts b ON b.link_post_id=l.link_post_id WHERE l.member_id=@member_id;