0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@kotaro_ai_labin株式会社Good Lab 開発部

【SQL実践ドリル 第6回】テーブル設計の基本 ― CREATE TABLE・制約・正規化

0
Posted at

はじめに

【SQL実践ドリル】 シリーズ第6回では、DDL（Data Definition Language：データ定義言語） とテーブル設計の基本を扱います。適切なテーブル設計はデータの整合性とアプリケーションの信頼性に直結します。データ型の選択、制約の設定、正規化の考え方を身につけましょう。

このシリーズでは共通のサンプルデータベースを使用します。初回（第1回）でCREATE TABLE文を掲載しています。

サンプルデータの概要

テーブル 件数 主な列
departments 5件 department_id, department_name, location
employees 10件 employee_id, name, department_id, hire_date, salary, manager_id
customers 5件 customer_id, customer_name, email, prefecture, registered_date
products 8件 product_id, product_name, category, price, stock
orders 10件 order_id, customer_id, employee_id, order_date
order_details 20件 detail_id, order_id, product_id, quantity

DDLの基礎知識

主なデータ型（MySQL）

データ型 説明 用途例
INT 整数（-2147483648 〜 2147483647） ID、数量、年齢
BIGINT 大きな整数 大規模システムのID
VARCHAR(n) 可変長文字列（最大n文字） 名前、メールアドレス
TEXT 長い文字列（最大65535バイト） 記事本文、コメント
DATE 日付（YYYY-MM-DD） 入社日、誕生日
DATETIME 日時（YYYY-MM-DD HH:MM:SS） 作成日時、更新日時
DECIMAL(m,d) 固定小数点数（m桁、小数部d桁） 金額、税率
BOOLEAN 真偽値（MySQLではTINYINT(1)） フラグ、有効/無効

MySQL と PostgreSQL の違い

  • MySQL: BOOLEAN は TINYINT(1) のエイリアス（0がFALSE、1がTRUE）。
  • PostgreSQL: 真の BOOLEAN 型（TRUE/FALSE）。
  • MySQL: AUTO_INCREMENT で自動採番。
  • PostgreSQL: SERIAL または GENERATED ALWAYS AS IDENTITY で自動採番。
  • MySQL: DATETIME は タイムゾーン情報を持たない。TIMESTAMP はUTCで保存される。
  • PostgreSQL: TIMESTAMP WITH TIME ZONE（TIMESTAMPTZ）でタイムゾーン対応。

主な制約

制約 説明
PRIMARY KEY 主キー。NOT NULLかつUNIQUE。テーブルに1つ。
FOREIGN KEY 外部キー。参照先テーブルの値のみ許可。
NOT NULL NULL値を禁止。
UNIQUE 重複値を禁止。NULLは複数可（MySQL）。
DEFAULT デフォルト値を設定。
CHECK 値の条件を指定（MySQL 8.0.16以降で対応）。

正規化の概要

正規形 条件
第1正規形（1NF） すべての列が原子的（1つのセルに1つの値）。繰り返し項目がない。
第2正規形（2NF） 1NFを満たし、部分関数従属がない（主キーの一部だけで決まる列がない）。
第3正規形（3NF） 2NFを満たし、推移的関数従属がない（非キー列が別の非キー列に依存しない）。

問題

問1 ⭐（基本）：ブログシステムのテーブルを設計する

以下の要件を満たす users テーブルと posts テーブルを CREATE TABLE で作成してください。

users テーブル：

  • user_id: 整数、主キー、自動採番
  • username: 可変長文字列（最大50文字）、NOT NULL、ユニーク
  • email: 可変長文字列（最大100文字）、NOT NULL、ユニーク
  • created_at: 日時、デフォルトは現在日時

posts テーブル：

  • post_id: 整数、主キー、自動採番
  • user_id: 整数、NOT NULL、usersテーブルへの外部キー
  • title: 可変長文字列（最大200文字）、NOT NULL
  • body: TEXT型、NOT NULL
  • published: BOOLEAN型、デフォルトはFALSE
  • created_at: 日時、デフォルトは現在日時
  • updated_at: 日時、デフォルトは現在日時（更新時に自動更新）
模範解答

MySQL：

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    post_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    body TEXT NOT NULL,
    published BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

PostgreSQL の場合：

CREATE TABLE users (
    user_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    post_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    body TEXT NOT NULL,
    published BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);
-- PostgreSQLではON UPDATE CURRENT_TIMESTAMPがないため、トリガーで実現します

解説：

  • AUTO_INCREMENT（MySQL）/ GENERATED ALWAYS AS IDENTITY（PostgreSQL）で主キーを自動採番。
  • ON DELETE CASCADE により、ユーザーが削除されると関連する投稿も自動削除されます。
  • MySQLの ON UPDATE CURRENT_TIMESTAMP は行が更新されるたびに updated_at を自動更新します。PostgreSQLではトリガーを作成する必要があります。
  • BOOLEAN DEFAULT FALSE は MySQL では TINYINT(1) DEFAULT 0 と同等です。

問2 ⭐（基本）：既存テーブルに制約を追加する

サンプルデータベースの employees テーブルに対して、以下の変更を行うALTER TABLE文を書いてください。

  1. salary 列に CHECK制約を追加: 給与は 0 以上でなければならない
  2. name 列に NOT NULL 制約を追加（既にNOT NULLでない想定）
  3. hire_date 列にデフォルト値として現在日付を設定
模範解答 
-- 1. salary に CHECK 制約を追加
ALTER TABLE employees
ADD CONSTRAINT chk_salary_positive CHECK (salary >= 0);

-- 2. name に NOT NULL 制約を追加
ALTER TABLE employees
MODIFY COLUMN name VARCHAR(100) NOT NULL;

-- 3. hire_date にデフォルト値を設定
ALTER TABLE employees
ALTER COLUMN hire_date SET DEFAULT (CURRENT_DATE);

PostgreSQL の場合：

-- 1. salary に CHECK 制約を追加
ALTER TABLE employees
ADD CONSTRAINT chk_salary_positive CHECK (salary >= 0);

-- 2. name に NOT NULL 制約を追加
ALTER TABLE employees
ALTER COLUMN name SET NOT NULL;

-- 3. hire_date にデフォルト値を設定
ALTER TABLE employees
ALTER COLUMN hire_date SET DEFAULT CURRENT_DATE;

確認用クエリ（CHECK制約のテスト）：

-- これはエラーになるはず
INSERT INTO employees (employee_id, name, department_id, hire_date, salary, manager_id)
VALUES (99, 'テスト太郎', 1, '2025-01-01', -100, NULL);
-- ERROR: Check constraint 'chk_salary_positive' is violated.

解説：

  • CHECK制約は MySQL 8.0.16 以降で利用可能です。それ以前のバージョンでは構文は受け付けますが無視されるため注意が必要です。
  • MySQLで NOT NULL を追加する場合は MODIFY COLUMN を使い、列の型定義全体を再指定する必要があります。
  • PostgreSQLでは ALTER COLUMN ... SET NOT NULL で個別に変更できます。
  • 制約にはわかりやすい名前（chk_salary_positive）を付けましょう。エラーメッセージでの特定が容易になります。

問3 ⭐（基本）：ALTER TABLE ― 列の追加・変更・削除

employees テーブルに対して以下の変更を行うSQL文をそれぞれ書いてください。

  1. phone 列を追加（VARCHAR(20)、NULL許可）
  2. phone 列の型を VARCHAR(15) に変更
  3. phone 列を削除
模範解答 
-- 1. 列の追加
ALTER TABLE employees
ADD COLUMN phone VARCHAR(20);

-- 2. 列の型変更
ALTER TABLE employees
MODIFY COLUMN phone VARCHAR(15);

-- 3. 列の削除
ALTER TABLE employees
DROP COLUMN phone;

PostgreSQL の場合：

-- 1. 列の追加
ALTER TABLE employees
ADD COLUMN phone VARCHAR(20);

-- 2. 列の型変更
ALTER TABLE employees
ALTER COLUMN phone TYPE VARCHAR(15);

-- 3. 列の削除
ALTER TABLE employees
DROP COLUMN phone;

解説：

  • ADD COLUMN は両DBで共通です。
  • 型の変更は MySQL が MODIFY COLUMN、PostgreSQL が ALTER COLUMN ... TYPE です。
  • DROP COLUMN は両DBで共通です。
  • 本番環境で列を削除する場合は、アプリケーション側で該当列を参照しなくなったことを確認してから実行してください。
  • MySQLでは ALTER TABLE は暗黙的にテーブルロックを取得するため、大規模テーブルでは pt-online-schema-change や gh-ost の利用を検討してください。

問4 ⭐⭐（応用）：第1正規形（1NF）への正規化

以下の非正規形テーブルを第1正規形に変換してください。

非正規形テーブル order_raw：

order_id customer_name products
1 株式会社ABC ノートPC Pro×2, ワイヤレスマウス×5
2 DEFコーポレーション 4Kモニター×1, メカニカルキーボード×3
3 株式会社ABC ノートPC Light×1, USBハブ 7ポート×2

問題点： products 列に複数の値がカンマ区切りで入っており、1NFを満たしていません。

模範解答

正規化後のテーブル設計：

CREATE TABLE order_normalized (
    order_id INT,
    customer_name VARCHAR(100) NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (order_id, product_name)
);

INSERT INTO order_normalized (order_id, customer_name, product_name, quantity)
VALUES
    (1, '株式会社ABC', 'ノートPC Pro', 2),
    (1, '株式会社ABC', 'ワイヤレスマウス', 5),
    (2, 'DEFコーポレーション', '4Kモニター', 1),
    (2, 'DEFコーポレーション', 'メカニカルキーボード', 3),
    (3, '株式会社ABC', 'ノートPC Light', 1),
    (3, '株式会社ABC', 'USBハブ 7ポート', 2);

正規化後のテーブル：

order_id customer_name product_name quantity
1 株式会社ABC ノートPC Pro 2
1 株式会社ABC ワイヤレスマウス 5
2 DEFコーポレーション 4Kモニター 1
2 DEFコーポレーション メカニカルキーボード 3
3 株式会社ABC ノートPC Light 1
3 株式会社ABC USBハブ 7ポート 2

解説：

第1正規形（1NF）の条件はすべての列が原子的な値を持つことです。元のテーブルでは products 列に「ノートPC Pro×2, ワイヤレスマウス×5」のように複数の値が入っていました。これを1行1商品に分解し、商品名と数量を別の列にしたことで1NFを満たします。

ただし、この時点でまだ customer_name が order_id に依存して重複しています（2NFの問題）。これは次の問題で扱います。

問5 ⭐⭐（応用）：第2正規形・第3正規形への正規化

問4の正規化後テーブルにはまだ問題があります。以下の拡張データを見て、第3正規形まで正規化してください。

拡張データ order_flat：

order_id customer_name customer_pref product_name category price quantity
1 株式会社ABC 東京都 ノートPC Pro パソコン 198000 2
1 株式会社ABC 東京都 ワイヤレスマウス 周辺機器 3500 5
2 DEFコーポレーション 大阪府 4Kモニター モニター 45000 1
2 DEFコーポレーション 大阪府 メカニカルキーボード 周辺機器 12000 3

正規化の問題点を指摘し、第3正規形に分解したテーブルをCREATE TABLE文で書いてください。

模範解答

問題点の分析：

  1. 2NFの違反（部分関数従属）： 主キーが (order_id, product_name) の場合、customer_name と customer_pref は order_id のみで決まる（部分関数従属）。
  2. 3NFの違反（推移的関数従属）： product_name → category, price（product_name が決まれば category と price が決まる。非キー列間の依存）。

第3正規形に分解：

-- 顧客テーブル
CREATE TABLE customers_3nf (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    customer_pref VARCHAR(50)
);

INSERT INTO customers_3nf (customer_id, customer_name, customer_pref)
VALUES
    (1, '株式会社ABC', '東京都'),
    (2, 'DEFコーポレーション', '大阪府');

-- 商品テーブル
CREATE TABLE products_3nf (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price INT NOT NULL
);

INSERT INTO products_3nf (product_id, product_name, category, price)
VALUES
    (1, 'ノートPC Pro', 'パソコン', 198000),
    (2, 'ワイヤレスマウス', '周辺機器', 3500),
    (4, '4Kモニター', 'モニター', 45000),
    (5, 'メカニカルキーボード', '周辺機器', 12000);

-- 注文テーブル
CREATE TABLE orders_3nf (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers_3nf(customer_id)
);

INSERT INTO orders_3nf (order_id, customer_id)
VALUES (1, 1), (2, 2);

-- 注文明細テーブル
CREATE TABLE order_details_3nf (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders_3nf(order_id),
    FOREIGN KEY (product_id) REFERENCES products_3nf(product_id)
);

INSERT INTO order_details_3nf (order_id, product_id, quantity)
VALUES
    (1, 1, 2),
    (1, 2, 5),
    (2, 4, 1),
    (2, 5, 3);

確認用クエリ（元のフラットなデータを復元できることを確認）：

SELECT
    o.order_id,
    c.customer_name,
    c.customer_pref,
    p.product_name,
    p.category,
    p.price,
    od.quantity
FROM order_details_3nf od
JOIN orders_3nf o ON od.order_id = o.order_id
JOIN customers_3nf c ON o.customer_id = c.customer_id
JOIN products_3nf p ON od.product_id = p.product_id
ORDER BY o.order_id, p.product_id;

解説：

正規化のステップを整理します。

  • 1NF → 2NF： 部分関数従属を排除。customer_name, customer_pref は order_id のみで決まるため、注文テーブルと顧客テーブルに分離。
  • 2NF → 3NF： 推移的関数従属を排除。category, price は product_name で決まるため、商品テーブルに分離。

結果として、サンプルデータベースと同じ構造（customers, products, orders, order_details の4テーブル構成）になります。これがリレーショナルデータベースの正規化の典型的なパターンです。

問6 ⭐⭐（応用）：ECサイトのカテゴリ階層を設計する

ECサイトの商品カテゴリを階層構造で管理するテーブルを設計してください。以下の階層を表現できるようにしてください。

電子機器
  ├── パソコン
  │   ├── ノートPC
  │   └── デスクトップPC
  ├── 周辺機器
  │   ├── マウス
  │   └── キーボード
  └── モニター

テーブル設計とサンプルデータの INSERT文を書いてください。

模範解答

隣接リストモデル（Adjacency List）：

CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL,
    parent_id INT DEFAULT NULL,
    display_order INT NOT NULL DEFAULT 0,
    FOREIGN KEY (parent_id) REFERENCES categories(category_id)
        ON DELETE CASCADE
);

INSERT INTO categories (category_id, category_name, parent_id, display_order)
VALUES
    (1, '電子機器', NULL, 1),
    (2, 'パソコン', 1, 1),
    (3, '周辺機器', 1, 2),
    (4, 'モニター', 1, 3),
    (5, 'ノートPC', 2, 1),
    (6, 'デスクトップPC', 2, 2),
    (7, 'マウス', 3, 1),
    (8, 'キーボード', 3, 2);

テーブルの内容：

category_id category_name parent_id display_order
1 電子機器 NULL 1
2 パソコン 1 1
3 周辺機器 1 2
4 モニター 1 3
5 ノートPC 2 1
6 デスクトップPC 2 2
7 マウス 3 1
8 キーボード 3 2

子カテゴリを含めた一覧を取得するクエリ：

-- 親カテゴリ名を含めて取得
SELECT
    c.category_id,
    c.category_name,
    p.category_name AS parent_name
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.category_id
ORDER BY COALESCE(c.parent_id, c.category_id), c.display_order;

期待される結果：

category_id category_name parent_name
1 電子機器 NULL
2 パソコン 電子機器
3 周辺機器 電子機器
4 モニター 電子機器
5 ノートPC パソコン
6 デスクトップPC パソコン
7 マウス 周辺機器
8 キーボード 周辺機器

MySQL 8.0 以降：再帰CTEで全階層を取得：

WITH RECURSIVE category_tree AS (
    -- ルートカテゴリ
    SELECT category_id, category_name, parent_id, 0 AS depth,
           CAST(category_name AS CHAR(500)) AS path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 子カテゴリを再帰的に結合
    SELECT c.category_id, c.category_name, c.parent_id, ct.depth + 1,
           CONCAT(ct.path, ' > ', c.category_name)
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT category_id, category_name, depth, path
FROM category_tree
ORDER BY path;

期待される結果：

category_id category_name depth path
1 電子機器 0 電子機器
2 パソコン 1 電子機器 > パソコン
5 ノートPC 2 電子機器 > パソコン > ノートPC
6 デスクトップPC 2 電子機器 > パソコン > デスクトップPC
3 周辺機器 1 電子機器 > 周辺機器
7 マウス 2 電子機器 > 周辺機器 > マウス
8 キーボード 2 電子機器 > 周辺機器 > キーボード
4 モニター 1 電子機器 > モニター

解説：

自己参照（自分自身への外部キー）は階層データの代表的な設計パターンです。parent_id が NULL のレコードがルートノードになります。

隣接リストモデルの特徴：

  • 利点: シンプルな設計、ノードの追加・移動が容易
  • 欠点: 祖先や子孫の全取得にはCTE（再帰クエリ）が必要

他の階層モデルとしてパス列挙モデル（path列に "/1/2/5/" のように格納）や入れ子集合モデル（左値・右値で範囲管理）もあります。

問7 ⭐⭐（応用）：正規化の問題点を指摘する

以下のテーブル設計の正規化上の問題点を指摘し、改善案を示してください。

CREATE TABLE employee_projects (
    employee_id INT,
    employee_name VARCHAR(100),
    department_name VARCHAR(50),
    project_name VARCHAR(100),
    project_start_date DATE,
    project_end_date DATE,
    role VARCHAR(50),
    PRIMARY KEY (employee_id, project_name)
);

サンプルデータ：

employee_id employee_name department_name project_name project_start_date project_end_date role
1 田中太郎 営業部 Webリニューアル 2024-04-01 2024-09-30 リーダー
1 田中太郎 営業部 モバイルアプリ 2024-07-01 2025-03-31 メンバー
2 佐藤花子 開発部 Webリニューアル 2024-04-01 2024-09-30 開発者
2 佐藤花子 開発部 基幹システム刷新 2024-10-01 2025-06-30 リーダー
模範解答

問題点の分析：

  1. 2NFの違反（部分関数従属）：

    • employee_name, department_name は employee_id のみで決まる（主キーの一部 project_name に依存しない）。
    • project_start_date, project_end_date は project_name のみで決まる（主キーの一部 employee_id に依存しない）。

  2. 3NFの違反（推移的関数従属）：

    • employee_id → department_name（employee_id が決まれば部署が決まるが、department_name は非キー列）。

  3. 更新異常のリスク：

    • 田中太郎の部署名を変更する場合、2行とも更新が必要（更新異常）。
    • 「Webリニューアル」の期間を変更する場合も2行更新が必要。

改善後のテーブル設計：

-- 社員テーブル
CREATE TABLE employees_improved (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_name VARCHAR(50) NOT NULL
);

INSERT INTO employees_improved VALUES
    (1, '田中太郎', '営業部'),
    (2, '佐藤花子', '開発部');

-- プロジェクトテーブル
CREATE TABLE projects (
    project_id INT AUTO_INCREMENT PRIMARY KEY,
    project_name VARCHAR(100) NOT NULL UNIQUE,
    start_date DATE NOT NULL,
    end_date DATE
);

INSERT INTO projects (project_id, project_name, start_date, end_date) VALUES
    (1, 'Webリニューアル', '2024-04-01', '2024-09-30'),
    (2, 'モバイルアプリ', '2024-07-01', '2025-03-31'),
    (3, '基幹システム刷新', '2024-10-01', '2025-06-30');

-- プロジェクトアサインテーブル（中間テーブル）
CREATE TABLE project_assignments (
    employee_id INT,
    project_id INT,
    role VARCHAR(50) NOT NULL,
    PRIMARY KEY (employee_id, project_id),
    FOREIGN KEY (employee_id) REFERENCES employees_improved(employee_id),
    FOREIGN KEY (project_id) REFERENCES projects(project_id)
);

INSERT INTO project_assignments VALUES
    (1, 1, 'リーダー'),
    (1, 2, 'メンバー'),
    (2, 1, '開発者'),
    (2, 3, 'リーダー');

確認用クエリ（元のデータを復元）：

SELECT
    e.employee_id,
    e.employee_name,
    e.department_name,
    p.project_name,
    p.start_date AS project_start_date,
    p.end_date AS project_end_date,
    pa.role
FROM project_assignments pa
JOIN employees_improved e ON pa.employee_id = e.employee_id
JOIN projects p ON pa.project_id = p.project_id
ORDER BY e.employee_id, p.project_name;

解説：

3つのテーブルに分解することで以下が改善されます。

  • 社員情報（employee_name, department_name）は1箇所で管理 → 更新異常を防止
  • プロジェクト情報（project_name, start_date, end_date）も1箇所で管理
  • 社員とプロジェクトの関係（多対多）は中間テーブル（project_assignments）で管理
  • role は社員×プロジェクトの組み合わせごとに異なるため、中間テーブルに配置するのが適切

問8 ⭐⭐⭐（チャレンジ）：外部キー制約の動作を設計する

以下のシナリオに対して、適切な外部キー制約（ON DELETE / ON UPDATE）を設定してください。理由も述べてください。

シナリオ：

  1. departments と employees の関係: 部署が削除された場合、社員の department_id はどうすべきか？
  2. orders と order_details の関係: 注文が削除された場合、注文明細はどうすべきか？
  3. users と login_history の関係: ユーザーが削除された場合、ログイン履歴はどうすべきか？
模範解答 
-- 1. departments → employees: SET NULL
-- 理由: 部署が廃止されても社員のデータは残すべき。
-- department_id を NULL にして「未所属」として扱う。
ALTER TABLE employees
ADD CONSTRAINT fk_emp_dept
FOREIGN KEY (department_id) REFERENCES departments(department_id)
    ON DELETE SET NULL
    ON UPDATE CASCADE;

-- 2. orders → order_details: CASCADE
-- 理由: 注文が削除されたら、その明細も不要。
-- 親子関係が密接なため CASCADE が適切。
ALTER TABLE order_details
ADD CONSTRAINT fk_od_order
FOREIGN KEY (order_id) REFERENCES orders(order_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

-- 3. users → login_history: 監査要件による
-- パターンA: ログイン履歴を保持する場合（監査目的）
CREATE TABLE login_history_a (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    login_at DATETIME NOT NULL,
    ip_address VARCHAR(45),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

-- パターンB: ユーザーと一緒に削除する場合
CREATE TABLE login_history_b (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    login_at DATETIME NOT NULL,
    ip_address VARCHAR(45),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

外部キーの ON DELETE / ON UPDATE オプション一覧：

オプション 動作 使いどころ
CASCADE 親の変更に追従（削除/更新） 親子の密結合（注文と明細）
SET NULL NULL に設定 参照先消失を許容（部署と社員）
RESTRICT 子データがあれば親の変更を拒否 データ保護を最優先
NO ACTION RESTRICT と同等（MySQL） デフォルト動作
SET DEFAULT デフォルト値に設定 MySQLのInnoDBでは非対応

解説：

外部キー制約の設計はビジネス要件に直結します。

  • CASCADE: 「親が消えたら子も不要」→ 注文と注文明細
  • SET NULL: 「親が消えても子は残すが、関係は切れる」→ 部署と社員
  • RESTRICT: 「子がいる限り親を消せない」→ 在庫がある商品のカテゴリ削除防止

実務では RESTRICT をデフォルトにし、意図的な削除のみ CASCADE や SET NULL を設定するアプローチが安全です。

問9 ⭐⭐⭐（チャレンジ）：DROP TABLE と依存関係

以下の順序でテーブルを削除するSQL文を書いてください。外部キー制約を考慮し、正しい順序で削除してください。

テーブル一覧（外部キー関係）：

  • order_details → orders（order_id）
  • order_details → products（product_id）
  • orders → customers（customer_id）
  • orders → employees（employee_id）
  • employees → departments（department_id）
模範解答 
-- 外部キーの依存関係に従い、子テーブルから順に削除する
-- (参照している側 → 参照されている側 の順)

-- 1. 最も多くの外部キーを持つ子テーブルを先に削除
DROP TABLE IF EXISTS order_details;

-- 2. order_details が削除されたので orders を削除可能
DROP TABLE IF EXISTS orders;

-- 3. orders が削除されたので customers, products, employees を削除可能
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS products;

-- 4. employees が削除されたので departments を削除可能
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;

別解（外部キーチェックを一時的に無効化する方法 ― MySQL）：

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS departments;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS order_details;

SET FOREIGN_KEY_CHECKS = 1;

PostgreSQL の場合：

-- CASCADE オプションで依存する制約も一緒に削除
DROP TABLE IF EXISTS order_details CASCADE;
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS customers CASCADE;
DROP TABLE IF EXISTS products CASCADE;
DROP TABLE IF EXISTS employees CASCADE;
DROP TABLE IF EXISTS departments CASCADE;

解説：

外部キー制約がある場合、参照されている側（親テーブル）を先に削除するとエラーになります。正しい削除順序は以下のルールに従います。

  1. 他のテーブルを参照しているが、他から参照されていないテーブルを先に削除
  2. すべての子テーブルが削除されたら、親テーブルを削除

DROP TABLE IF EXISTS を使うことで、テーブルが存在しない場合のエラーを防げます。

SET FOREIGN_KEY_CHECKS = 0（MySQL）を使えば順序を気にせず削除できますが、本番環境での使用は推奨されません。テスト環境やマイグレーションスクリプトでの利用に留めてください。

問10 ⭐⭐⭐（チャレンジ）：総合問題 ― レビューシステムのテーブル設計

ECサイトに商品レビュー機能を追加します。以下の要件を満たすテーブルを第3正規形で設計し、CREATE TABLE文を書いてください。

要件：

  • 顧客が購入した商品に対してレビューを投稿できる
  • レビューには評価（1〜5の整数）とコメント（任意）を含む
  • 同一顧客が同一商品に対してレビューは1件のみ
  • レビューの投稿日時と更新日時を記録する
  • 既存の customers テーブルと products テーブルを参照する
模範解答 
CREATE TABLE reviews (
    review_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    rating INT NOT NULL,
    comment TEXT,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_customer_product (customer_id, product_id),
    CONSTRAINT fk_review_customer
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_review_product
        FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON DELETE CASCADE,
    CONSTRAINT chk_rating
        CHECK (rating >= 1 AND rating <= 5)
);

PostgreSQL の場合：

CREATE TABLE reviews (
    review_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    rating INT NOT NULL,
    comment TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (customer_id, product_id),
    CONSTRAINT fk_review_customer
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_review_product
        FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON DELETE CASCADE,
    CONSTRAINT chk_rating
        CHECK (rating >= 1 AND rating <= 5)
);

サンプルデータの投入：

INSERT INTO reviews (customer_id, product_id, rating, comment)
VALUES
    (1, 1, 5, '性能が素晴らしいです。仕事用に最適。'),
    (1, 2, 4, 'コスパが良い。電池の持ちも良好。'),
    (2, 4, 3, '画質は良いが、スタンドの調整が難しい。'),
    (3, 1, 4, NULL),
    (4, 5, 5, 'タイピングが快適になりました。');

確認用クエリ（商品ごとの平均評価）：

SELECT
    p.product_name,
    COUNT(r.review_id) AS review_count,
    AVG(r.rating) AS avg_rating
FROM products p
LEFT JOIN reviews r ON p.product_id = r.product_id
GROUP BY p.product_id, p.product_name
HAVING COUNT(r.review_id) > 0
ORDER BY avg_rating DESC;

期待される結果：

product_name review_count avg_rating
メカニカルキーボード 1 5.0000
ノートPC Pro 2 4.5000
ワイヤレスマウス 1 4.0000
4Kモニター 27インチ 1 3.0000

解説：

設計のポイントを整理します。

  • UNIQUE KEY (customer_id, product_id): 同一顧客・同一商品のレビューを1件に制限。2回目の投稿はUNIQUE制約違反でエラーになります。
  • CHECK (rating >= 1 AND rating <= 5): 評価値の範囲を制約で保証。アプリケーション側のバリデーションだけでなく、DB側でも制約を設けることでデータの整合性を強化します。
  • comment TEXT: NULLを許可しているため、コメントなしの評価も可能です。
  • ON DELETE CASCADE: 顧客や商品が削除されたらレビューも自動削除。ビジネス要件によっては RESTRICT にして「レビューがある商品は削除不可」にすることも考えられます。
  • 第3正規形の確認: review_id で全列が決まり（2NF）、非キー列間の推移的依存もない（3NF）。customer_id と product_id は外部キーで別テーブルの情報を参照しています。

確認結果の計算:

  • メカニカルキーボード: 1件、評価5 → 平均5.0
  • ノートPC Pro: 2件、評価5+4=9 → 平均4.5
  • ワイヤレスマウス: 1件、評価4 → 平均4.0
  • 4Kモニター 27インチ: 1件、評価3 → 平均3.0

まとめ

カテゴリ 構文 / 概念 ポイント
テーブル作成 CREATE TABLE 適切なデータ型と制約を選択
テーブル変更 ALTER TABLE ADD/MODIFY/DROP COLUMN
テーブル削除 DROP TABLE 外部キーの依存順序に注意
制約 PK, FK, NOT NULL, UNIQUE, DEFAULT, CHECK DB側でデータ整合性を保証
第1正規形 すべての列が原子的 繰り返し項目を排除
第2正規形 部分関数従属がない 主キーの一部で決まる列を分離
第3正規形 推移的関数従属がない 非キー列間の依存を分離

テーブル設計はアプリケーション開発の基盤です。正規化でデータの重複と更新異常を防ぎつつ、パフォーマンス要件に応じて適度に非正規化するバランスが重要です。

参考

@kotaro_ai_lab
AI活用や開発効率化について発信しています。フォローお気軽にどうぞ！

0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?