MySQL & MariaDBの世界

MySQL主キー（PRIMARY KEY）の基本からエラー解決まで、初心者向け完全解説

MySQL
佐川 直弘 | Naohiro Sagawa
By 佐川 直弘 | Naohiro Sagawa (運営者)
※記事内に広告を含みます。Amazonアソシエイトとして適格販売により収入を得ています。　
目次

1. はじめに

データベースの設計において、「主キー（PRIMARY KEY）」は欠かせない概念です。
主キーは、各データの一意性を保証する重要な役割を果たし、効率的なデータ管理や整合性を支える基盤となります。

この記事では、MySQLにおける主キーの基本から設定方法、エラー解決、設計のコツまでを詳しく解説します。初心者から中級者まで、幅広いユーザーに役立つ内容を目指しました。

このガイドを読み終えるころには、次のことができるようになります：

  • 主キーの基本概念を理解する。
  • 主キーを設定し、エラーを解決する。
  • データベース設計において最適な主キーを選択する。

それでは、主キーの基本から学んでいきましょう。

2. 主キー（PRIMARY KEY）とは？

データベースにおける「主キー（PRIMARY KEY）」とは、一意性を保証するためにテーブル内で使用される重要なキーの一種です。主キーはデータの識別子として機能し、各行を一意に特定します。

主キーの特徴

  1. 一意性を保証
    主キーに設定された列の値は、テーブル内で重複することができません。
  2. NULL値の非許容
    主キーは必ず値が設定されている必要があり、NULLを許容しません。
  3. テーブルごとに1つのみ設定可能
    各テーブルに設定できる主キーは1つだけで、複数の主キーを設定することはできません。ただし、複数の列を組み合わせて1つの主キーにする「複合主キー」は可能です。

主キーが必要な理由

主キーを設定することで、以下のようなメリットがあります：

  • データの整合性を確保：同じデータが重複して登録されるのを防ぎます。
  • 効率的な検索：データの検索や更新、削除の際に効率が向上します。
  • データの参照関係を管理：外部キーを使用する際、主キーが基準となります。

例を挙げると、ユーザー情報を管理するテーブルで「ユーザーID」を主キーに設定することで、各ユーザーを一意に識別できます。

3. MySQLでの主キーの設定方法

MySQLでは、主キー（PRIMARY KEY）を簡単に設定することができます。以下では、新しいテーブルを作成する際の主キー設定方法と、既存のテーブルに主キーを追加する方法を具体的な例を交えて解説します。

新規テーブル作成時の主キー設定

新しいテーブルを作成する際に、主キーを設定するには、CREATE TABLE文を使用します。主キーを定義する列には、NOT NULL制約を付与する必要があります。

基本的な例

次の例では、usersというテーブルを作成し、id列を主キーに設定しています。

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    PRIMARY KEY (id)
);

解説：

  • id列にはAUTO_INCREMENTを設定し、自動的に連続したユニークな値を生成するようにしています。
  • PRIMARY KEY (id)で、id列を主キーとして指定しています。

複数の列を主キーに設定する（複合主キー）

複合主キーを設定することで、複数の列の組み合わせによって一意性を保証できます。

例：複合主キーの設定

CREATE TABLE orders (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

解説：

  • order_idとproduct_idの組み合わせを主キーに指定しています。
  • 同じorder_idでも、異なるproduct_idを持つデータを登録できます。

既存のテーブルに主キーを追加する方法

既存のテーブルに主キーを後から追加する場合、ALTER TABLE文を使用します。

例：単一列を主キーに追加

以下は、既存のusersテーブルにid列を主キーとして追加する例です。

ALTER TABLE users ADD PRIMARY KEY (id);

例：複合主キーを追加

複数の列を組み合わせて主キーを設定する場合も同様です。

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

主キーの削除方法

主キーを削除するには、以下のようにALTER TABLE文を使用します。

ALTER TABLE users DROP PRIMARY KEY;

注意：

  • 主キーを削除すると、それに基づいて作成されたインデックスも削除されるため、慎重に操作してください。

自動増分（AUTO_INCREMENT）の活用

主キーと連携してよく使用されるのが、AUTO_INCREMENTです。この設定を行うと、データを挿入するたびに値が自動的に増加します。

例：AUTO_INCREMENTを使用した主キー

CREATE TABLE customers (
    customer_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (customer_id)
);

4. 複合主キーの活用

複合主キーとは、複数の列を組み合わせて1つの主キーとして設定し、データの一意性を保証する方法です。特に、単一の列では一意性を担保できない場合や、特定のビジネスルールに基づいてデータを識別する必要がある場合に役立ちます。

複合主キーの基本概念

複合主キーを使用すると、2つ以上の列の値の組み合わせが一意であることを保証できます。たとえば、注文データを管理するテーブルでは、注文ID（order_id）と商品ID（product_id）を組み合わせることで、各注文の商品を一意に識別できます。

複合主キーの設定方法

新規テーブル作成時の設定

以下は、ordersテーブルで複合主キーを設定する例です。

CREATE TABLE orders (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

解説：

  • order_idとproduct_idを組み合わせて主キーとして指定しています。
  • 同じorder_idであっても、異なるproduct_idを持つレコードが登録可能です。

既存テーブルへの複合主キーの追加

既存のテーブルに複合主キーを追加する場合は、以下のようにALTER TABLE文を使用します。

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

複合主キーの利点

  1. 一意性の保証
  • 複数の列を組み合わせて、一意性を保つことができます。
  1. ビジネスルールに適応
  • 例えば、注文情報を管理する場合、order_idだけでは一意性を保証できないが、order_idとproduct_idを組み合わせることでデータの整合性を保つことが可能です。
  1. データの管理が簡潔に
  • 複合主キーを使用することで、重複データを防ぎ、データ構造をシンプルに保つことができます。

複合主キーの注意点

  1. パフォーマンスの影響
  • 複合主キーは、検索や挿入時に複数の列を比較するため、単一主キーよりも処理コストが高くなる場合があります。特に大規模なデータベースでは注意が必要です。
  1. インデックスの利用制限
  • 複合主キーが設定された場合、自動生成されるインデックスは複数の列を対象とします。個別の列でインデックスを使用したい場合は、別途インデックスを作成する必要があります。
  1. 設計の複雑化
  • 複数の列を組み合わせることで、設計が複雑になる可能性があります。適切なカラム選択が重要です。

複合主キーを使うべき場面

  • 複数の条件で一意性を管理する必要がある場合
    例：注文IDと商品ID、またはプロジェクトIDとタスクIDなど。
  • 単一の列では一意性が保証できない場合
    例：1人の顧客が複数の商品を購入する場合、顧客IDと購入日を組み合わせて一意性を管理する。

5. 主キーとインデックスの違い

主キー（PRIMARY KEY）とインデックス（INDEX）は、MySQLで重要な概念ですが、それぞれの役割や目的は異なります。このセクションでは、主キーとインデックスの違いを解説し、それぞれの特徴や使い方について詳しく説明します。

主キー（PRIMARY KEY）の特徴

  1. 一意性の保証
  • 主キーは、テーブル内の各行を一意に識別します。
  • 重複やNULL値を許容しません。
  1. データ整合性の確保
  • 主キーを設定することで、テーブルの一貫性とデータの整合性が維持されます。
  1. 自動的にインデックスが作成される
  • 主キーを設定すると、自動的にインデックスが作成され、検索やデータ操作の効率が向上します。

主キーの例

以下は、id列を主キーとして設定した例です。

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50),
    PRIMARY KEY (id)
);

インデックス（INDEX）の特徴

  1. 検索速度の向上
  • インデックスは、データ検索やクエリの実行を高速化します。
  1. 一意性は保証しない
  • 通常のインデックスでは、一意性は保証されません（ただし、ユニークインデックスは一意性を保証します）。
  1. 任意の列に設定可能
  • インデックスは主キー以外の列にも設定でき、特定のクエリを最適化します。

インデックスの例

以下は、username列にインデックスを追加する例です。

CREATE INDEX idx_username ON users (username);

主キーとインデックスの違い

特徴主キー（PRIMARY KEY）インデックス（INDEX）
一意性を保証するかする通常はしない（ユニークインデックスを除く）
NULL値を許容するか許容しない許容する
自動生成されるか主キーを設定すると自動的にインデックスが作成される手動で作成する必要がある
設定可能な列の数テーブルごとに1つ複数設定可能

主キーとインデックスの関係

  • 主キーは自動的にインデックスを持つ
    主キーを設定すると、自動的にユニークインデックスが作成されます。このため、主キーの列は高速に検索できます。
  • 主キー以外にもインデックスを設定できる
    主キー以外の列にインデックスを設定することで、特定のクエリの性能を向上させることができます。

例：主キーとインデックスの両方を使用

以下の例では、主キーと別の列にインデックスを設定しています。

CREATE TABLE products (
    product_id INT NOT NULL AUTO_INCREMENT,
    product_name VARCHAR(100),
    category_id INT,
    PRIMARY KEY (product_id),
    INDEX idx_category (category_id)
);

解説：

  • product_idは主キーであり、一意性とNULL非許容が保証されています。
  • category_idにはインデックスが設定され、カテゴリごとの商品検索が高速化されます。

主キーとインデックスを使い分けるポイント

  1. 主キーの選定
  • データを一意に識別する列を主キーに設定します。
  • 主キーは必ずテーブルに1つ設定します。
  1. インデックスの活用
  • 主キー以外の列で頻繁に検索条件として使用される場合、インデックスを設定します。
  • 例：usernameやemailなど、クエリでよく使用する列。
  1. 注意点
  • インデックスを設定しすぎると、データ挿入や更新時のパフォーマンスが低下する可能性があります。必要最低限のインデックス設定が推奨されます。

6. 主キーに関する注意点

主キー（PRIMARY KEY）はデータベース設計において非常に重要な役割を果たしますが、正しく設計・管理しなければ、パフォーマンスや整合性に問題が生じる可能性があります。このセクションでは、主キーに関する注意点とベストプラクティスについて解説します。

主キーの削除や変更に関する注意点

主キーの削除方法

主キーを削除するには、ALTER TABLE文を使用します。

ALTER TABLE users DROP PRIMARY KEY;

注意点：

  1. 主キーを削除すると、それに基づいて作成されたインデックスも同時に削除されます。
  2. 主キーに依存する外部キー制約が存在する場合、削除操作は失敗します。事前に外部キーの制約を解除する必要があります。

主キーの変更方法

既存の主キーを変更するには、次の手順を実行します：

  1. 現在の主キーを削除する。
  2. 新しい主キーを追加する。

例：

ALTER TABLE users DROP PRIMARY KEY;
ALTER TABLE users ADD PRIMARY KEY (username);

主キーの選定に関する注意点

  1. 適切なカラムを選ぶ
  • 主キーには一意性を保証できるカラムを選択します。
  • よく使われる選択肢：
    • 自動増分（AUTO_INCREMENT）付きの数値型（例：id）
    • 自然キー（例：社員番号や商品コード）
  1. 更新頻度の高いカラムを避ける
  • 主キーに設定したカラムの値を頻繁に更新すると、パフォーマンスに悪影響を与える場合があります。
  1. 長い文字列を避ける
  • 長い文字列（例：200文字の文字列型）を主キーに設定すると、検索や操作の効率が低下します。
  1. NULL値が含まれるカラムを避ける
  • 主キーはNULL値を許容しないため、値が必ず存在するカラムを選ぶ必要があります。

主キー設定時のエラーとその対処法

  1. エラー：ERROR 1068 (42000): Multiple primary key defined
  • 原因：
    • 複数の主キーを定義しようとした場合に発生します。
  • 解決策：
    • 重複する主キー定義を削除してください。
      sql ALTER TABLE users DROP PRIMARY KEY; ALTER TABLE users ADD PRIMARY KEY (id);
  1. エラー：ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
  • 原因：
    • 外部キー制約が主キーと一致しない場合に発生します。
  • 解決策：
    • 主キーと外部キーのデータ型や制約を一致させます。
  1. エラー：ERROR 1265 (01000): Data truncated for column
  • 原因：
    • 主キーに指定されたデータが期待される型や長さを超えている場合に発生します。
  • 解決策：
    • 主キー列のデータ型を適切に変更する、または挿入データを修正します。

主キー設定時のベストプラクティス

  1. 単一カラムを優先する
  • 単一カラムの主キー（例：AUTO_INCREMENT付きの数値型）を使用することで、シンプルで効率的な設計が可能です。
  1. 複合主キーの適切な利用
  • 複数の列を組み合わせることで、必要最小限のカラム数に留めることが重要です。
  1. 主キー列のデータ型を最適化
  • 数値型（例：INT）や短い文字列（例：VARCHAR(50)）を選択し、パフォーマンスを向上させます。
  1. データの一貫性を保つ
  • 主キー列には一意性と整合性を維持するため、適切な入力データチェックを行います。

7. 主キーを設計する際のベストプラクティス

データベース設計において、主キー（PRIMARY KEY）の適切な設計は、データの一貫性やパフォーマンスに直接影響を与えます。このセクションでは、主キーを効果的に設計するためのベストプラクティスを解説します。

自然キー vs. 人工キー：どちらを選ぶべきか？

自然キーとは？

自然キーは、既存のビジネスデータを主キーとして利用するものです。
例：社員番号、商品コード、メールアドレスなど。

利点：

  • データの意味が直感的にわかる。
  • 既存のシステムや業務ルールと一致しやすい。

欠点：

  • 長い文字列や頻繁に変更される値が含まれる場合、パフォーマンスに影響を与える。
  • データの重複が発生するリスクがある。

人工キーとは？

人工キーは、データベース内で生成される一意の識別子です。
例：AUTO_INCREMENTを使用した数値型のID。

利点：

  • 常に一意性が保証される。
  • 検索や更新が効率的。
  • 値が変更されるリスクがない。

欠点：

  • 人間には意味を持たない値であるため、データの理解に役立たない。

どちらを選ぶべきか？

  • 人工キーを推奨：ほとんどのケースで人工キー（例：AUTO_INCREMENT付きの数値型ID）を使用するのがベストです。
  • 自然キーを選ぶ場合：ビジネスルール上、自然キーの使用が必要であり、データの整合性が保証されている場合に限ります。

主キーのパフォーマンス最適化

  1. 数値型を使用する
  • 主キーにはINTやBIGINTなどの数値型を使用することで、検索や比較処理が高速化されます。
  • 例：
    sql CREATE TABLE customers ( customer_id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100), PRIMARY KEY (customer_id) );
  1. 最小限のサイズにする
  • 主キーのサイズを小さく保つことで、ストレージの使用量を削減し、インデックスのパフォーマンスを向上させます。
  • 例：INT（4バイト）を使用するのが一般的で、BIGINT（8バイト）は大規模データの場合に限ります。
  1. 主キーの更新を避ける
  • 主キーを更新すると、関連するインデックスや外部キー制約も更新されるため、大きなパフォーマンス低下を引き起こす可能性があります。

主キーと外部キーの設計

  • 主キーは、外部キー（FOREIGN KEY）の参照先としてよく利用されます。そのため、外部キー制約を設計する際には、主キーの一意性と整合性が重要です。

例：主キーと外部キーの設計

CREATE TABLE customers (
    customer_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (customer_id)
);

CREATE TABLE orders (
    order_id INT NOT NULL AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE,
    PRIMARY KEY (order_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

解説：

  • ordersテーブルのcustomer_id列が、customersテーブルのcustomer_id列を参照しています。
  • 主キーの整合性が保たれていない場合、外部キー制約がエラーを引き起こす可能性があります。

主キー設計時のチェックリスト

  1. 主キーは必ず一意性を保証できる列か？
  2. 主キーに長い文字列や頻繁に変更される列を使用していないか？
  3. 自然キーよりも人工キーの使用が適切かどうか検討したか？
  4. 主キー列のデータ型は適切か（INTやBIGINTなど）？
  5. 複合主キーを使用する場合、列数を最小限に抑えているか？

主キーを適切に設計することで、データベースのパフォーマンスが向上し、データの整合性を保つことができます。次のセクションでは、「8. FAQ（よくある質問）」を通じて、読者が抱きやすい疑問に答えていきます。

8. FAQ（よくある質問）

ここでは、MySQLの主キー（PRIMARY KEY）に関して、よくある質問に答えていきます。初心者から中級者までが疑問に思うポイントを分かりやすく解説します。

主キーとユニークキーの違いは何ですか？

主キー（PRIMARY KEY）:

  • 各行を一意に識別します。
  • 1つのテーブルに1つしか設定できません。
  • NULL値を許容しません。

ユニークキー（UNIQUE KEY）:

  • 各行の値を一意に保ちますが、テーブル内で複数設定可能です。
  • NULL値を許容します（ただし、NULLも一意として扱われます）。

例：

CREATE TABLE example (
    id INT NOT NULL AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE,
    username VARCHAR(50),
    PRIMARY KEY (id)
);
  • id列が主キー。
  • email列がユニークキー。

主キーを設定しないとどうなりますか？

主キーを設定しない場合：

  1. データの一意性を保証できません。
  2. 外部キー制約を設定できなくなります。
  3. テーブルのデータ検索や操作が非効率になる可能性があります。

推奨:

  • すべてのテーブルに主キーを設定することを推奨します。

複合主キーを使うべき場面は？

複合主キーは、複数の列の組み合わせで一意性を保証する必要がある場合に使用します。

例：

  • 注文データを管理する場合、order_idとproduct_idの組み合わせで一意性を保証。
CREATE TABLE orders (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

注意:

  • 複合主キーは設計を複雑にするため、慎重に検討する必要があります。

自動増分（AUTO_INCREMENT）は必須ですか？

必須ではありませんが、次のような場面で便利です：

  • 主キーとして使用する列の値を自動的に生成したい場合。
  • 一意性を保証する数値型の主キーが必要な場合。

例:

CREATE TABLE users (
    user_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (user_id)
);

主キーの値を変更するにはどうすればいいですか？

主キーの値を直接変更することは可能ですが、次の点に注意が必要です：

  1. 外部キー制約がある場合、関連するデータにも影響を与える可能性があります。
  2. 更新コストが高くなるため、パフォーマンスに悪影響を及ぼす場合があります。

例:

UPDATE users SET user_id = 10 WHERE user_id = 1;

主キーがパフォーマンスに与える影響は？

利点:

  • 主キーは自動的にインデックスが付与されるため、データ検索や整列が高速化されます。

注意点:

  • 主キー列のデータ型や値の長さが適切でない場合、パフォーマンスが低下する可能性があります。

主キーに最適なデータ型は？

  • 数値型（INTまたはBIGINT）が最も推奨されます。
  • コンパクトで検索が高速。
  • 自動増分（AUTO_INCREMENT）と組み合わせて利用可能。
  • 文字列を使用する場合は、VARCHARを推奨しますが、長さを短く保つことが重要です。

主キーを複数設定できますか？

1つのテーブルには1つの主キーしか設定できません。ただし、複数の列を組み合わせて複合主キーを作成することは可能です。

主キーの削除や変更時にエラーが発生する場合の対処法は？

よくあるエラー：

  • 外部キー制約が原因で主キーの削除や変更ができない。

解決策：

  1. 外部キー制約を削除します。
  2. 主キーを変更した後、再度外部キー制約を設定します。

例:

ALTER TABLE orders DROP FOREIGN KEY fk_customer_id;
ALTER TABLE orders DROP PRIMARY KEY;
ALTER TABLE orders ADD PRIMARY KEY (new_id);

9. まとめ

この記事では、MySQLにおける主キー（PRIMARY KEY）の基本から設定方法、注意点、ベストプラクティス、そしてFAQまでを詳しく解説しました。主キーは、データベース設計において欠かせない要素であり、その適切な設定がデータの一意性とパフォーマンスに大きな影響を与えます。

主キーの重要性を振り返る

  1. データの一意性を保証
    主キーは、テーブル内の各行を一意に識別し、データの重複を防ぎます。
  2. データ検索や操作の効率化
    主キーには自動的にインデックスが付与されるため、検索やデータ操作が高速化されます。
  3. 外部キー制約の基盤
    主キーを設定することで、外部キー制約を利用したデータの整合性管理が可能になります。

設計時の重要なポイント

  • 主キーに適切な列を選ぶ
    一意性を保証し、頻繁に変更されない列を主キーに設定することが重要です。
  • 複合主キーの慎重な利用
    必要な場合のみ複合主キーを利用し、設計を複雑にしすぎないよう注意します。
  • 適切なデータ型の選択
    主キーにはINTやBIGINTなどの数値型を使用することで、効率的なパフォーマンスを実現します。

今後の学習ポイント

主キーの理解を深めたら、次のトピックについても学ぶことで、さらに効果的なデータベース設計が可能になります。

  1. 外部キー（FOREIGN KEY）の設定
    データ間の関係を構築し、整合性を保つ方法を学びましょう。
  2. インデックスの最適化
    主キー以外の列にインデックスを設定することで、クエリのパフォーマンスを向上させる技術を身につけましょう。
  3. 正規化と非正規化
    データベース設計の基礎である正規化や、パフォーマンス向上のための非正規化を検討しましょう。

最後に

MySQLの主キーを正しく設計することは、データベース全体の効率性と信頼性を向上させるための第一歩です。この記事を参考にして、適切な主キーを設定し、管理するためのスキルを磨いてください。主キーの設計がしっかりとできていれば、データベースの構築や運用がスムーズになり、業務全体の生産性向上にもつながるでしょう。

これでこの記事は終了です。ぜひこの記事を参考に、実際のプロジェクトで効果的な主キーの設計を行ってみてください。