구체 테이블 상속

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 ORDER BY post_type;

member_id | post_type | post_id
1         | IMAGE     | 888888
1         | TEXT      | 999999

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' AS post_type, text_post_id AS post_id, title,
       canonical_url AS target_url, body_chars AS 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);

SELECT post_type, post_id, title, display_hint
FROM concrete_post_feed_v;

post_type | post_id | title              | display_hint
TEXT      | 1       | InnoDB 인덱스 읽기 | 25

INSERT INTO member_text_post VALUES (@member_id, 999999);

SELECT l.member_id, l.text_post_id FROM member_text_post AS l
LEFT JOIN text_posts AS 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 AS l
LEFT JOIN image_posts AS 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 AS l
LEFT JOIN link_posts AS 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' AS kind, a.text_post_id AS id, a.title
FROM member_text_post AS l
JOIN text_posts AS 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 AS l
JOIN image_posts AS 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 AS l
JOIN link_posts AS b ON b.link_post_id = l.link_post_id
WHERE l.member_id = @member_id;

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`board_lab`.`member_text_post`, CONSTRAINT `member_text_post_ibfk_2` ...)

kind | id | title
TEXT | 1  | InnoDB 인덱스 읽기