MySQLのテーブル設計と制約 | MySQL入門

一行の意味を決める

設計の最初の問いは「このテーブルの一行は何を表すか」です。productsなら一行が一商品、ordersなら一行が一注文です。注文に複数商品が含まれるなら、注文全体と商品の明細を別の表へ分けます。

列を増やす前に考える

「商品1」「商品2」のような列を増やすと、商品の個数に上限ができ、検索や集計も難しくなります。繰り返し登場するデータは別テーブルにするのが基本です。

キーと制約

主キーは一行を特定し、外部キーは他テーブルに存在する行との関係を守ります。UNIQUEはメールアドレスなどの重複を防ぎ、CHECKは値の範囲を制限します。

顧客テーブルMYSQL

CREATE TABLE customers (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_customers_email (email),
    CONSTRAINT chk_customers_age CHECK (age >= 0 AND age <= 150)
) ENGINE = InnoDB;

違反を確認する例MYSQL

INSERT INTO customers (email, name, age)
VALUES ('a@example.com', 'Aさん', 20);

INSERT INTO customers (email, name, age)
VALUES ('a@example.com', '別の人', 30);

実行結果OUTPUT

ERROR 1062 (23000): Duplicate entry 'a@example.com' for key 'customers.uq_customers_email'

CHECK制約にも違反させるMYSQL

INSERT INTO customers (email, name, age)
VALUES ('b@example.com', 'Bさん', 151);

実行結果OUTPUT

ERROR 3819 (HY000): Check constraint 'chk_customers_age' is violated.

エラー番号、SQLSTATE、制約名を読むと、どのルールで拒否されたかを特定できます。エラーは失敗ではなく、決めたデータルールが働いた結果です。

注文と明細へ分ける

一つの注文に複数商品を保存するMYSQL

CREATE TABLE order_items (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    quantity INT UNSIGNED NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id),
    CHECK (quantity > 0)
) ENGINE = InnoDB;

注文時点の価格をunit_priceへ保存すると、商品の現在価格が変わっても過去の注文金額を再現できます。どの値を履歴として残すかは、業務要件から決めます。

インデックスを作る

インデックスは検索や結合の候補を素早く見つけるための構造です。ただし追加・更新時の負担と保存容量も増えるので、すべての列へ作るものではありません。

よく検索する列に追加MYSQL

CREATE INDEX idx_products_category
    ON products (category);

EXPLAIN SELECT name, price
FROM products
WHERE category = 'book';

EXPLAINの結果はデータ量や統計情報で変わります。インデックスを作っただけで速いと決めつけず、実際のクエリーとデータ量で確認します。

設計を確認する

構造と制約を表示MYSQL

DESCRIBE products;
SHOW CREATE TABLE products;
SHOW INDEX FROM products;

設計を変更したら、サンプルデータで追加、検索、更新、削除、結合が動くか確認します。制約違反のテストも用意すると、将来の変更でルールが失われたことに気づきやすくなります。

MySQL入門の講座一覧

一行の意味を決める

キーと制約

注文と明細へ分ける

インデックスを作る

設計を確認する