MySQLの複合主キーの適切な作成方法

複合主キーとは？

MySQLでは、複数の列を組み合わせて主キーとして設定することができます。これを複合主キーと呼びます。複合主キーは、テーブル内の各行を一意に識別するために使用されます。

複合主キーを作成する際の注意点

1. 列の選択: 複合主キーとして使用する列は、常に一意の値を保持する必要があります。つまり、同じ組み合わせの値が別の行に存在してはなりません。
2. インデックス: 複合主キーは自動的にインデックスが作成されます。このインデックスは、クエリのパフォーマンスを向上させるために重要です。
3. 順序: 複合主キーの列の順序は重要です。異なる順序で比較すると、異なる結果が得られる場合があります。
4. NULL値: 複合主キーの列にNULL値が含まれることはできません。

複合主キーの作成例

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  product_id INT,
  order_date DATE,
  UNIQUE KEY (customer_id, product_id)
);

この例では、order_idが単一の主キーであり、customer_idとproduct_idの組み合わせが複合主キーとして設定されています。UNIQUE KEY制約を使用して、同じ顧客が同じ商品を複数回注文できないようにしています。

MyISAMエンジンと複合主キー

MyISAMエンジンでは、複合主キーのインデックスが固定長であり、テーブルのサイズに応じて事前に割り当てられます。そのため、複合主キーの列のデータ型が変更されると、テーブルの再構築が必要になることがあります。

InnoDBエンジンと複合主キー

MySQLの複合主キー解説とコード例

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  product_id INT,
  order_date DATE,
  UNIQUE KEY (customer_id, product_id)
);

複合主キーを使用したクエリ

-- 顧客IDと商品IDを指定して注文情報を取得
SELECT * FROM orders WHERE customer_id = 1 AND product_id = 2;

-- 顧客ごとの注文数を取得
SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id;

複合主キーの更新と削除

-- 注文情報を更新
UPDATE orders SET order_date = '2023-12-31' WHERE customer_id = 1 AND product_id = 2;

-- 注文を削除
DELETE FROM orders WHERE customer_id = 1 AND product_id = 2;

複合主キーの利点

• データの整合性: 複合主キーを使用することで、データの重複や矛盾を防ぐことができます。
• クエリのパフォーマンス: 複合主キーのインデックスは、クエリのパフォーマンスを向上させることができます。
• データの管理: 複合主キーを使用することで、データの管理が容易になります。

• 列の選択: 適切な列を選択することが重要です。
• インデックス: 適切なインデックスを作成することが重要です。
• NULL値: NULL値を使用することはできません。

単一の主キーとユニーク制約

• 単一の主キー: テーブルに単一の主キーを設定し、複合主キーの列にユニーク制約を適用します。
• 利点: シンプルで管理が容易です。
• 欠点: 複合主キーの列の順序が重要になります。

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  product_id INT,
  order_date DATE,
  UNIQUE KEY (customer_id, product_id)
);

自然キー

• 自然キー: テーブルのデータ自体が自然に一意の値を持つ列を主キーとして使用します。
• 利点: 意味的にわかりやすく、データの整合性を確保しやすいです。
• 欠点: 適切な自然キーが存在しない場合、人工的な主キーを使用する必要があります。

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(100),
  product_code VARCHAR(20) UNIQUE
);

代替キー

• 代替キー: テーブルに複数の主キーを設定し、それぞれを代替キーとして使用します。
• 利点: 複数の視点からデータを管理することができます。
• 欠点: 管理が複雑になる場合があります。

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  product_id INT,
  order_date DATE,
  UNIQUE KEY (customer_id, product_id)
);

ALTER TABLE orders ADD PRIMARY KEY (customer_id, product_id);

外部キー

• 外部キー: 他のテーブルの主キーを参照する列を外部キーとして設定します。
• 利点: データの整合性を確保し、関連するデータを簡単に取得することができます。
• 欠点: 外部キー制約が違反されると、データの挿入や更新が失敗します。

CREATE TABLE customers (
  customer_id INT PRIMARY KEY
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

複合インデックス

• 複合インデックス: 複数の列を組み合わせてインデックスを作成します。
• 利点: クエリのパフォーマンスを向上させることができます。
• 欠点: 複合インデックスの管理が複雑になる場合があります。

CREATE INDEX idx_customer_product ON orders (customer_id, product_id);