자연 키와 대리 키

USE board_lab;
DROP TABLE IF EXISTS post_integrity_demo;
DROP TABLE IF EXISTS member_daily_post_quota;
DROP TABLE IF EXISTS daily_post_stats;
DROP TABLE IF EXISTS member_point_accounts;
DROP TABLE IF EXISTS daily_post_quota_composite_demo;
DROP TABLE IF EXISTS attachment_reference_demo;

USE board_lab;

DROP TABLE IF EXISTS post_attachments;
DROP TABLE IF EXISTS attachments;
DROP TABLE IF EXISTS post_tags;
DROP TABLE IF EXISTS tags;
DROP TABLE IF EXISTS comments;
DROP TABLE IF EXISTS member_profiles;
DROP TABLE IF EXISTS daily_post_quota;
DROP TABLE IF EXISTS posts;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS member_external_identities;
DROP TABLE IF EXISTS members;

CREATE TABLE members (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(80) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_members_email (email),
  CONSTRAINT ck_members_status
    CHECK (status IN ('ACTIVE', 'SUSPENDED', 'DELETED'))
) ENGINE = InnoDB;

CREATE TABLE posts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  author_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(160) NOT NULL,
  content TEXT NOT NULL,
  status VARCHAR(16) NOT NULL DEFAULT 'DRAFT',
  view_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL
    DEFAULT CURRENT_TIMESTAMP(6)
    ON UPDATE CURRENT_TIMESTAMP(6),
  published_at DATETIME(6) NULL,
  version BIGINT UNSIGNED NOT NULL DEFAULT 0,
  request_key VARCHAR(64) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_posts_author_request (author_id, request_key),
  KEY ix_posts_author_created (author_id, created_at DESC, id DESC),
  CONSTRAINT ck_posts_status
    CHECK (status IN ('DRAFT', 'HIDDEN', 'PUBLISHED', 'DELETED')),
  CONSTRAINT fk_posts_author
    FOREIGN KEY (author_id)
    REFERENCES members (id)
) ENGINE = InnoDB;

INSERT INTO members (email, password_hash, name)
VALUES ('[email protected]', '$2a$12$example.hash', '안동민');

SET @member_id := LAST_INSERT_ID();

INSERT INTO posts
  (author_id, title, content, status, view_count, created_at, published_at, request_key)
VALUES
  (@member_id, '대리 키의 책임', '대표 식별자와 업무 키를 분리합니다.', 'PUBLISHED', 50,
   '2026-07-13 13:00:00.000000', '2026-07-13 13:00:00.000000', 'post-key-001'),
  (@member_id, '업무 키의 유일성', '이메일 중복은 별도 제약으로 막습니다.', 'PUBLISHED', 40,
   '2026-07-13 14:10:00.000000', '2026-07-13 14:10:00.000000', 'post-key-002');

UPDATE members
SET email = '[email protected]'
WHERE id = @member_id;

SELECT
  l.id AS member_id,
  l.email,
  s.id AS post_id,
  s.title
FROM members AS l
JOIN posts AS s
  ON s.author_id = l.id
WHERE l.id = @member_id
ORDER BY s.id;

member_id | email            | post_id | title
1          | [email protected]  | 1          | 대리 키의 책임
1          | [email protected]  | 2          | 업무 키의 유일성

INSERT INTO members (email, password_hash, name)
VALUES ('[email protected]', '$2a$12$duplicate.hash', '중복 사용자');

ERROR 1062 (23000): Duplicate entry '[email protected]'
for key 'members.uq_members_email'

CREATE TABLE member_external_identities (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  member_id BIGINT UNSIGNED NOT NULL,
  provider VARCHAR(30) NOT NULL,
  provider_subject VARCHAR(191) NOT NULL,
  linked_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uk_member_external_identities_provider_subject
    (provider, provider_subject),
  UNIQUE KEY uk_member_external_identities_member_provider
    (member_id, provider),
  KEY ix_member_external_identities_member (member_id),
  CONSTRAINT ck_member_external_identities_provider
    CHECK (provider IN ('github', 'google')),
  CONSTRAINT fk_member_external_identities_member
    FOREIGN KEY (member_id)
    REFERENCES members (id)
) ENGINE = InnoDB;

INSERT INTO member_external_identities
  (member_id, provider, provider_subject)
VALUES
  (@member_id, 'github', 'github-user-9182'),
  (@member_id, 'google', 'google-user-4410');

ALTER TABLE member_external_identities
  MODIFY provider_subject VARCHAR(255)
    CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NOT NULL;

INSERT INTO member_external_identities
  (member_id, provider, provider_subject)
VALUES
  (@member_id, 'github', 'github-user-9182');