SQLのテーブル設計 | SQL入門 | Mityの趣味のサイト

テーブル設計は何を守るのか

テーブル設計の目的は、現在の画面をそのまま保存することではありません。同じ事実を何度も保存せず、追加・変更・削除をしても矛盾しにくい構造を作ることです。

最初に整理すること

何を一件として管理するか
一件を一意に見分ける値は何か
必須の項目と、省略できる項目は何か
値の範囲や重複禁止のルールは何か
別のデータと一対一、一対多、多対多のどれで関係するか

主キーで一行を識別する

主キーは一行を一意に識別します。氏名は同姓同名があり、メールアドレスは変更される可能性があるため、用途に応じて変更されにくいIDを用意します。

顧客テーブルSQL

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

PRIMARY KEYには重複しないこととNULLでないことが求められます。業務上意味のある番号を使う方法と、データベース用の代理キーを作る方法があります。

制約で不正な値を拒否する

アプリの入力検証だけでなく、テーブルにもルールを書くと、別のツールから登録したときも不正な値を防げます。

制約を持つ商品テーブルSQL

CREATE TABLE safe_products (
    id INTEGER PRIMARY KEY,
    code TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    price INTEGER NOT NULL CHECK (price >= 0),
    stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

不正な価格を登録SQL

INSERT INTO safe_products (id, code, name, price)
VALUES (1, 'B001', 'SQL入門書', -100);

実行結果OUTPUT

Runtime error: CHECK constraint failed: price >= 0

エラー文の細部はSQLiteのバージョンやツールで異なりますが、行は追加されません。なおNOT NULLは空文字を禁止しないため、必要ならアプリ側の検証や追加のCHECKを考えます。

繰り返す情報を分ける

注文テーブルへ顧客名と顧客メールを毎回保存すると、同じ顧客の注文が増えるたびに情報が重複します。メール変更時に一部だけ直すと矛盾します。

分ける前の問題例 orders(id, customer_name, customer_email, product_name, quantity)では、顧客名や商品名が注文ごとに繰り返されます。

顧客、商品、注文を別々のテーブルにし、IDで関係付けます。一つのセルには一つの値を保存し、一つのテーブルには一種類の事実をまとめる、と考えるのが出発点です。

役割ごとに分けた注文SQL

CREATE TABLE customer_orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    ordered_at TEXT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES customer_orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

注文と商品の多対多の関係をorder_itemsという中間テーブルで表しています。複合主キーにより、同じ注文内で同じ商品が重複するのを防ぎます。このルールが要件に合わない場合は明細IDを別に用意します。

外部キーで存在する行だけを参照する

外部キーは、参照先に存在しないIDを拒否します。SQLiteでは接続ごとに外部キー検査を有効にします。

外部キーを有効化して確認SQL

PRAGMA foreign_keys = ON;

INSERT INTO customer_orders (id, customer_id, ordered_at)
VALUES (1, 999, '2026-09-02');

実行結果OUTPUT

Runtime error: FOREIGN KEY constraint failed

顧客削除時に注文をどうするかも設計します。履歴を残すなら削除を禁止または論理削除にし、付随データも消してよい場合だけON DELETE CASCADEを検討します。

検索に必要なインデックスを作る

インデックスは本の索引のように、条件に合う行を探しやすくします。外部キーや頻繁に検索する列が候補です。

顧客と日付で注文を探す索引SQL

CREATE INDEX idx_customer_orders_customer_date
ON customer_orders (customer_id, ordered_at);

作りすぎない インデックスは保存容量を使い、追加や更新のたびに保守されます。少量の練習データでは効果を判断せず、実際の問い合わせと実行計画を確認して追加します。

PRACTICE

ミニ課題：カテゴリを分離する

カテゴリ名を商品ごとに文字列で持たず、categoriesテーブルへ分けてください。カテゴリIDは主キー、名前は必須かつ重複禁止とし、商品から外部キーで参照します。

解答例SQL

CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE designed_products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price INTEGER NOT NULL CHECK (price >= 0),
    category_id INTEGER,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

未分類を許すためcategory_idはNULL可能にしています。必ず分類する要件ならNOT NULLを追加します。

← 結合と副問い合わせ更新とトランザクションへ →