공통 코드 테이블

DROP TABLE IF EXISTS post_code_bad;

CREATE TABLE post_code_bad (
  post_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  post_status VARCHAR(30) NOT NULL,
  visibility VARCHAR(30) NOT NULL,
  view_count SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (post_id)
);

INSERT INTO post_code_bad
  (post_status, visibility, view_count)
VALUES
  ('PUBLISHED', 'PUBLIC', 30),
  ('published', 'manual', 40),
  ('게시', '직접입력', 50);

SELECT COUNT(*) AS published_count,
       SUM(view_count) AS published_views
FROM post_code_bad
WHERE post_status = 'PUBLISHED';

published_count | published_views
2               | 70

DROP TABLE IF EXISTS final_post_code_demo;
DROP TABLE IF EXISTS final_code;
DROP TABLE IF EXISTS final_code_group;

CREATE TABLE final_code_group (
  group_code VARCHAR(40) NOT NULL,
  group_name VARCHAR(100) NOT NULL,
  description VARCHAR(300) NOT NULL,
  PRIMARY KEY (group_code)
) ENGINE=InnoDB;

CREATE TABLE final_code (
  group_code VARCHAR(40) NOT NULL,
  code VARCHAR(40) NOT NULL,
  display_name_ko VARCHAR(100) NOT NULL,
  sort_order SMALLINT UNSIGNED NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (group_code, code),
  CONSTRAINT fk_final_code_group FOREIGN KEY (group_code)
    REFERENCES final_code_group (group_code) ON DELETE RESTRICT,
  CONSTRAINT uq_final_code_order UNIQUE (group_code, sort_order),
  CONSTRAINT chk_final_code_active CHECK (is_active IN (0, 1))
) ENGINE=InnoDB;

INSERT INTO final_code_group
  (group_code, group_name, description)
VALUES
  ('MEMBER_STATUS', '회원 상태', '가입·정지·탈퇴 수명주기'),
  ('POST_STATUS', '게시글 상태', '초안·게시·숨김·삭제 수명주기'),
  ('POST_VISIBILITY', '게시글 공개 범위', '전체·회원·작성자 공개 범위');

INSERT INTO final_code
  (group_code, code, display_name_ko, sort_order)
VALUES
  ('MEMBER_STATUS', 'ACTIVE', '활성', 10),
  ('MEMBER_STATUS', 'SUSPENDED', '정지', 20),
  ('MEMBER_STATUS', 'DELETED', '탈퇴', 30),
  ('POST_STATUS', 'DRAFT', '초안', 10),
  ('POST_STATUS', 'PUBLISHED', '게시됨', 20),
  ('POST_STATUS', 'HIDDEN', '숨김', 30),
  ('POST_STATUS', 'DELETED', '삭제됨', 40),
  ('POST_VISIBILITY', 'PUBLIC', '전체 공개', 10),
  ('POST_VISIBILITY', 'MEMBERS_ONLY', '회원 공개', 20),
  ('POST_VISIBILITY', 'PRIVATE', '작성자 전용', 30);

CREATE TABLE final_post_code_demo (
  post_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  status_group_code VARCHAR(40) NOT NULL DEFAULT 'POST_STATUS',
  post_status VARCHAR(40) NOT NULL,
  visibility_group_code VARCHAR(40) NOT NULL DEFAULT 'POST_VISIBILITY',
  visibility_code VARCHAR(40) NOT NULL,
  view_count SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (post_id),
  CONSTRAINT chk_demo_status_group
    CHECK (status_group_code = 'POST_STATUS'),
  CONSTRAINT chk_demo_visibility_group
    CHECK (visibility_group_code = 'POST_VISIBILITY'),
  CONSTRAINT chk_demo_view_count
    CHECK (view_count BETWEEN 0 AND 65535),
  CONSTRAINT fk_demo_status FOREIGN KEY
    (status_group_code, post_status)
    REFERENCES final_code (group_code, code),
  CONSTRAINT fk_demo_visibility FOREIGN KEY
    (visibility_group_code, visibility_code)
    REFERENCES final_code (group_code, code)
) ENGINE=InnoDB;

INSERT INTO final_post_code_demo
  (post_status, visibility_code, view_count)
VALUES ('PUBLISHED', 'PUBLIC', 30);

SELECT
  (SELECT COUNT(*) FROM final_code_group) AS code_groups,
  (SELECT COUNT(*) FROM final_code) AS detail_codes,
  (SELECT COUNT(*) FROM final_post_code_demo) AS post_rows;

code_groups | detail_codes | post_rows
3           | 10           | 1

INSERT INTO final_post_code_demo
  (post_status, visibility_code, view_count)
VALUES ('PUBLISH', 'PUBLIC', 20);

INSERT INTO final_post_code_demo
  (status_group_code, post_status, visibility_code, view_count)
VALUES ('POST_VISIBILITY', 'PUBLIC', 'PRIVATE', 20);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`board_lab`.`final_post_code_demo`, CONSTRAINT `fk_demo_status` ...)
ERROR 3819 (HY000): Check constraint 'chk_demo_status_group' is violated.

START TRANSACTION;

UPDATE final_code
SET display_name_ko = '공개됨'
WHERE group_code = 'POST_STATUS'
  AND code = 'PUBLISHED';

SELECT d.post_id,
       d.post_status,
       status_code.display_name_ko AS status_name,
       visibility_code.display_name_ko AS visibility_name,
       d.view_count
FROM final_post_code_demo AS d
JOIN final_code AS status_code
  ON status_code.group_code = d.status_group_code
 AND status_code.code = d.post_status
JOIN final_code AS visibility_code
  ON visibility_code.group_code = d.visibility_group_code
 AND visibility_code.code = d.visibility_code;

ROLLBACK;

post_id | post_status | status_name | visibility_name | view_count
1       | PUBLISHED   | 공개됨      | 전체 공개       | 30

START TRANSACTION;

INSERT INTO final_post_code_demo
  (post_status, visibility_code, view_count)
VALUES ('published', 'PUBLIC', 10);

SELECT d.post_status, c.code AS parent_code
FROM final_post_code_demo AS d
JOIN final_code AS c
  ON c.group_code = d.status_group_code
 AND c.code = d.post_status
WHERE d.view_count = 10;

INSERT INTO final_post_code_demo
  (post_status, visibility_code, view_count)
VALUES ('PUBLISHED ', 'PUBLIC', 10);

ROLLBACK;

post_status | parent_code
published   | PUBLISHED

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